跳到主要内容

创建和使用 SPL 例程

本部分描述如何创建和使用 SPL 例程。SPL 例程是以 GBase 8s“存储过程语言”（SPL）编写的用户定义的例程。GBase 8s SPL 是提供流控制的 SQL 的扩展，诸如循环和分支。在数据库上有 Resource 权限的任何人都可创建 SPL 例程。

尽可能地解析和优化以 SQL 编写的例程，然后以可执行的格式存储在系统目录表中。对于 SQL 密集的任务，SQL 例程可能是一个好的选择。SPL 例程可执行以 C 或其他外部语言编写的例程，且外部的例程可执行 SPL 例程。

您可使用 SPL 例程来执行您可以 SQL 执行的任何任务，且可扩展您可单独使用 SQL 完成的任务。由于 SQPL 是数据库的本地语言，且当创建 SPL 例程而不是在运行时时，解析和优化 SPL 例程，对于某些任务，SPL 例程可提升性能。SPL 例程还可减少客户机应用程序与数据库服务器之间的流量并降低程序复杂度。

在 GBase 8s SQL 指南：语法 中描述每一 SPL 语句的语法。每一语句都配有示例。

SPL 例程介绍​

SPL 例程 是包括 SPL 过程 和 SPL 函数 的一个广义术语。SPL 过程是以 SPL 和 SQL 编写的不返回值的例程。SPL 函数是以 SPL 和 SQL 编写的返回单个值、复合数据类型的值或多个值的例程。通常，以 SPL 编写的返回一个值的例程是 SPL 函数。

使用 SQL 和 SPL 语句来编写 SPL 例程。仅可在 CREATE PROCEDURE、CREATE PROCEDURE FROM、CREATE FUNCTION 和 CREATE FUNCTION FROM 语句内使用 SPL 语句。使用诸如 GBase 8s ESQL/C 这样的 SQL API 都可用所有这些语句。使用 DB-Access 可用 CREATE PROCEDURE 和 CREATE FUNCTION 语句。

要在数据库中罗列所有 SPL 例程，请运行此命令，该命令创建和显示数据库的模式：

dbschema -d database_name -f all

使用 SPL 例程可做什么​

使用 SPL 例程，您可实现广泛的目标，包括提升数据库性能，简化应用程序编写，以及限制或监视对数据的访问。

由于以可执行的格式存储 SPL 例程，您可使用它来频繁地执行反复的任务以提升性能。当您执行 SPL 例程而不是直接的 SQL 代码时，您可绕过反复的解析、有效性检查以及查询优化。

您可在数据操纵 SQL 语句中使用 SPL 例程来为那个语句提供值。例如，您可使用例程来执行下列操作：

  • 提供要插入到表内的值
  • 提供一个值，该值是组成 SELEC、DELETE 或 UPDATE 语句中条件子句的一部分

这些操作是在数据操作语句中例程的两种可能的使用，但也存在其他的。实际上，数据操纵 SQL 语句中的任何表达式都可由例程调用构成。

您还可在 SPL 例程中发出 SQL 语句来对数据库用户隐藏那些 SQL 语句。不是让所有用户都了解如何使用 SQL，一位有经验的 SQL 用户可编写 SPL 例程来封装 SQL 活动，并让其他人了解在该数据库中存储着该例程，以便他们可以执行它。

您可编写 SPL 例程，由不具有 DBA 权限的用户使用 DBA 权限来运行它。此特征允许您限制和控制对数据库中数据的访问。另外，SPL 例程可监视访问某些表或数据的用户。

SPL 例程格式​

SPL 例程由开始语句、语句块和结束语句组成。在语句块内，您可使用 SQL 或 SPL 语句。

CREATE PROCEDURE 或 CREATE FUNCTION 语句​

您必须首先决定您正在创建的例程是否返回值。如果例程不返回值，则使用 CREATE PROCEDURE 语句来创建一个 SPL 过程。如果例程返回一个值，则使用 CREATE FUNCTION 语句来创建一个 SPL 函数。

要创建 SPL 例程，请使用一个 CREATE PROCEDURE 或 CREATE FUNCTION 语句来编写该例程体，并注册它。

开始和结束例程​

要创建不返回值的 SPL 例程，请使用 CREATE PROCEDURE 语句开始，并以 END PROCEDURE 关键字结束。下图展示如何开始和结束 SPL 过程。

图: 开始和结束 SPL 例程。

CREATE PROCEDURE new_price( per_cent REAL )
. . .
END PROCEDURE;

要获取关于命名约定的更多信息，请参阅《GBase 8s SQL 指南：语法》 中的“标识符”段。

要创建返回一个或多个值的 SPL 函数，请使用 CREATE FUNCTION 语句开始，并以 END FUNCTION 关键字结束。 下图展示如何开始和结束 SPL 函数。

图: 开始和结束 SPL 函数。

CREATE FUNCTION discount_price( per_cent REAL)
RETURNING MONEY;
. . .
END FUNCTION;

在 SPL 例程中，END PROCEDURE 或 END FUNCTION 关键字是必需的。

重要

为了与较早的 GBase 8s 产品相兼容，您可使用带有 RETURNING 子句的 CREATE PROCEDURE 来创建返回值的用户定义的例程。然而，如果您对于不返回值的 SPL 例程（SPL 过程）使用 CREATE PROCEDURE，而对于返回一个或多个值的 SPL 例程（SPL 函数）使用 CREATE FUNCTION，则您的代码会更易于阅读和维护，

指定例程名称​

紧跟在 CREATE PROCEDURE 或 CREATE FUNCTION 语句之后，且在参数列表之前为 SPL 例程指定名称，如图所示。

图: 为 SPL 例程指定名称。

CREATE PROCEDURE add_price (arg INT )

GBase 8s 允许您以相同的名称但以不同的参数创建多个 SPL 例程。此特性称为例程重载。例如，您可能在您的数据库中创建下列每一 SPL 例程：

CREATE PROCEDURE multiply (a INT, b FLOAT)
CREATE PROCEDURE multiply (a INT, b SMALLINT)
CREATE PROCEDURE multiply (a REAL, b REAL)

如果您以名称 multiply() 调用例程，则数据库服务器评估该例程的名称和它的参数来确定执行哪个例程。

例程解析是数据库服务器在其中搜索它可使用的例程签名，给定例程的名称和参数列表的过程。每个例程都有一个基于下列信息唯一地标识该例程的签名：

  • 例程的类型（过程或函数）
  • 例程名称
  • 参数的数目
  • 参数的数据类型
  • 参数的顺序

如果您输入该例程的完整参数列表，则在 CREATE、DROP 或 EXECUTE 语句中使用该例程签名。例如，下图中的每一语句都使用例程签名。

图: 例程签名。

CREATE FUNCTION multiply(a INT, b INT);

DROP PROCEDURE end_of_list(n SET, row_id INT);

EXECUTE FUNCTION compare_point(m point, n point);

添加特定的名称​

由于 GBase 8s 支持例程重载，因此，不可能单独通过 SPL 例程的名称来唯一地标识它。然而，可通过特定的名称来唯一地标识例程。除了例程名称之外，特定的名称是您在 CREATE PROCEDURE 或 CREATE FUNCTION 语句中定义的唯一的标识符。使用 SPECIFIC 关键字来定义特定的名称，且该名称在数据库中是唯一的。在同一数据库中，不可有两个相同的特定的名称，即使它们有不同的所有者也不行。

特定的名称最长可达 128 字节。下图展示如何在创建 calculate() 函数的 CREATE FUNCTION 语句中定义特定的名称 calc1。

图: 定义特定的名称。

CREATE FUNCTION calculate(a INT, b INT, c INT)
RETURNING INT
SPECIFIC calc1;
. . .
END FUNCTION;

由于所有者 bsmith 已给定了 SPL 函数特定的名称 calc1，因此任何其他用户都不可使用特定的名称 calc1 来定义例程——SPL 或外部的。现在，您可引用该例程为 bsmith.calculate，或在任何需要 SPECIFIC 关键字的语句中使用 SPECIFIC 关键字 calc1。

添加参数列表​

当您创建 SPL 例程时，您可定义参数列表，以便当调用例程时，它接受一个或多个参数。参数列表是可选的。

SPL 例程的参数必须有名称，且可使用缺省值来定义。下列是参数可指定的数据类型的种类：

  • 内建的数据类型
  • Opaque 数据类型
  • Distinct 数据类型
  • Row 类型
  • 集合类型
  • 智能大对象（CLOB 和 BLOB）

参数列表不可直接地指定任一下列数据类型：

  • SERIAL
  • SERIAL8
  • BIGSERIAL
  • TEXT
  • BYTE

然而，对于序列数据类型，例程可返回在数值上对等的值，将它们强制转型为对应的整数类型（INT、INT8 或 BIGINT）。类似地，对于支持简单大对象数据类型的例程，参数列表可包括 REFERENCES 关键字来返回指向 TEXT 或 BYTE 对象的存储位置的描述符。

下图展示参数列表的示例。

图: 不同的参数列表的示例。

CREATE PROCEDURE raise_price(per_cent INT);

CREATE FUNCTION raise_price(per_cent INT DEFAULT 5);

CREATE PROCEDURE update_emp(n employee_t);

CREATE FUNCTION update_nums( list1 LIST(ROW (a VARCHAR(10),
b VARCHAR(10),
c INT) NOT NULL ));

当您定义参数时，同时完成两个任务：

  • 当执行例程时，您请求用户提供值。
  • 您隐式地定义您可在例程体中用作本地变量的变量（带有与参数名称相同的名称）。

如果您以缺省值定义参数，则用户可使用或不用对应的参数来执行该 SPL 例程。如果用户执行不带参数的 SPL 例程，则数据库服务器指定参数的缺省值作为参数。

当您调用 SPL 例程时，您可对参数给定 NULL 值。在缺省情况下，SPL 例程处理 NULL 值。然而，如果参数为集合元素，则您不可对该参数给定 NULL 值。

简单大对象作为参数​

虽然您不可使用简单大对象（包含 TEXT 或 BYTE 数据类型的大对象）来定义参数，但您可使用 REFERENCES 关键词来定义指向简单大对象的参数，如下图所示。

图: REFERENCES 关键字的使用。

CREATE PROCEDURE proc1(lo_text REFERENCES TEXT)

CREATE FUNCTION proc2(lo_byte REFERENCES BYTE DEFAULT NULL)

REFERENCES 关键字意味着将包含指向简单大对象的指针的描述符传给 SPL 例程，而不是传对象本身。

未定义的参数​

当您调用 SPL 例程时，您可指定全部或部分已定义的参数，或不指定参数。如果您未指定参数，且如果它的对应的参数没有缺省值，则给定在 SPL 例程内作为变量使用的该参数的状态为未定义的。

未定义是为没有值的 SPL 变量使用的特殊的状态。只要您在该例程体中不尝试使用状态为未定义的变量，该 SPL 例程就不会出错。

未定义的状态与 NULL 值不一样。（NULL 值意味着该值为未知的，或不存在，或不适用。）

添加返回子句​

如果您使用 CREATE FUNCTION 来创建 SPL 例程，您必须指定返回一个或多个值的返回子句。

提示： 如果您使用 CREATE PROCEDURE 语句来创建 SPL 例程，则您有指定返回子句的选项。然而，如果您使用 CREATE FUNCTION 语句来创建返回值的例程，则您的代码会比较易读且易于维护。

要指定返回子句，请使用带有该例程将返回的数据类型的列表的 RETURNING 或 RETURNS 关键字。数据类型可为除了 SERIAL、SERIAL8、TEXT 或 BYTE 之外的任何 SQL 数据类型。

下图中的返回子句指定，该 SPL 例程将返回 INT 值和 REAL 值。

图: 指定返回子句。

FUNCTION find_group(id INT)
RETURNING INT, REAL;
. . .
END FUNCTION;

在您指定返回子句之后，您还必须在例程体中指定 RETURN 语句，显式地返回调用例程的值。要获取关于编写 RETURN 语句的更多信息，请参阅从 SPL 函数返回值。

要指定应返回简单大对象（TEXT 或 BYTE 值）的函数，您必须使用 REFERENCES 子句，如下图所示，这是因为 SPL 例程仅返回指向该对象的指针，而不是该对象本身。

图: 使用 REFERENCES 子句。

CREATE FUNCTION find_obj(id INT)
RETURNING REFERENCES BYTE;

添加显示标签​

您可使用 CREATE FUNCTION 来创建例程，其为返回的值指定显示标签的名称。如果您未为显示标签指定名称，则该标签会显示为 expression。

此外，虽然对于返回值的例程推荐使用 CREATE FUNCTION，但您可使用 CREATE PROCEDURE 来创建返回值的例程，并指定返回的值的显示标签。

如果您选择为一个返回值指定显示标签，则您必须为每个返回值指定显示标签。此外，每一返回值必须有唯一的显示标签。

要添加显示标签，您必须指定返回子句，请使用 RETURNING 关键字。下图中的返回子句指定该例程将返回一个带有 serial_num 显示标签的 INT 值，一个带有 name 显示标签的 CHARE 值，以及一个带有 points 显示标签的 INT 值。您可使用下图中的 CREATE FUNCTION 或 CREATE PROCEDURE。

图: 指定返回子句。

CREATE FUNCTION p(inval INT DEFAULT 0)
RETURNING INT AS serial_num, CHAR (10) AS name, INT AS points;
RETURN (inval + 1002), "Newton", 100;
END FUNCTION;

在下图中展示返回的值和它们的显示标签。

图: 返回的值和它们的显示标签。

serial_num name points

1002 Newton 100
提示

由于您可在 SELECT 语句中直接地为返回值指定显示标签，因此，当在 SELECT 语句中使用 SPL 例程时，该标签会显示为 expression。要获取关于在 SELECT 语句中为返回值指定显示标签的更多内容，请参阅 编写 SELECT 语句。

指定 SPL 函数是否为变体​

当您创建 SPL 时，在缺省情况下，该函数为变体。当使用相同的参数调用函数时，如果它返回不同的结果，或如果它修改数据库或变量状态，则该函数为变体。例如，返回当前的日期或时间的函数是变体函数。

虽然在缺省情况下，SPL 函数为变体，但如果当您创建函数时指定 WITH NOT VARIANT，则该函数不可包含任何 SQL 语句。您仅可在非变体函数上定义函数索引。

添加修饰符​

当您编写 SPL 函数时，您可使用 WITH 子句来将修饰符添加到 CREATE FUNCTION 语句。在 WITH 子句中，您可指定 COMMUTATOR 或 NEGATOR 函数。其他修饰符是用于外部例程的。

限制： 您仅可以 SPL 函数使用 COMMUTATOR 或 NEGATOR 修饰符。您不可以 SPL 过程使用任何修饰符。

COMMUTATOR 修饰符​

COMMUTATOR 修饰符允许您指定 SPL 函数为您正在创建的 SPL 函数的转换函数。转换函数接受相同的参数作为您正在创建的 SPL 函数，但以相反的顺序，并返回相同值。对于 SQL 优化器的执行，转换函数的成本效益比更高。

例如，如果 a 小于 b，则函数 lessthan(a,b) 返回 TRUE， 而如果 b 大于或等于 a，则 greaterthan(b,a) 返回 TRUE， 二者是转换函数。 下图使用 WITH 子句来定义转换函数。

图: 定义转换函数。

CREATE FUNCTION lessthan( a dtype1, b dtype2 )
RETURNING BOOLEAN
WITH ( COMMUTATOR = greaterthan );
. . .
END FUNCTION;

如果 greaterthan(b,a) 的执行成本低于 lessthan(a,b)，则优化器可能使用 greaterthan(b,a)。要指定转换函数，您必须同时拥有该转换函数和您正在编写的 SPL 函数。您还必须将两个函数的 Execute 权限授予您的 SPL 函数的用户。

要了解授予权限的详细描述，请参阅《GBase 8s SQL 指南：语法》 中的 GRANT 语句的描述。

NEGATOR 修饰符​

NEGATOR 修饰符是用于布尔函数的变量。如果两个布尔函数的参数相同，顺序相同，且返回互补的布尔值，则它们是否定函数。

例如，如果 a 等于 b，则函数 equal(a,b) 返回 TRUE，而如果 a 等于 b，则 notequal(a,b) 返回 FALSE，二者是否定函数。如果您指定的否定函数的执行成本低于原始的函数，则优化器可能选择执行该否定函数。

下图展示如何使用 CREATE FUNCTION 语句的 WITH 子句来指定否定函数。

图: 指定否定函数。

CREATE FUNCTION equal( a dtype1, b dtype2 )
RETURNING BOOLEAN
WITH ( NEGATOR = notequal );
. . .
END FUNCTION;
提示

在缺省情况下，任何 SPL 例程都可处理在参数列表中传给其的 NULL 值。换言之，对于 SPL 例程，将 HANDLESNULLS 修饰符设置为 YES，且您不可更改它的值。

要获取关于 COMMUTATOR 和 NEGATOR 修饰符的更多信息，请参阅《GBase 8s SQL 指南：语法》 中的 Routine Modifier 段。

指定 DOCUMENT 子句​

DOCUMENT 和 WITH LISTING IN 子句跟在 END PROCEDURE 或 END FUNCTION 语句之后。

DOCUMENT 子句允许您将注释添加到您的 SPL 例程，另一例程可从系统目录表选择它，如果需要的话。下图中的 DOCUMENT 子句包含展示用户如何运行该 SPL 例程的用法语句。

图: 展示用户如何运行 SPL 例程的用法语句。

CREATE FUNCTION raise_prices(per_cent INT)
. . .
END FUNCTION
DOCUMENT "USAGE: EXECUTE FUNCTION raise_prices (xxx)",
"xxx = percentage from 1 - 100";

请记住在文字的子句两头放置单引号或双引号。如果文字的子句跨过一行，则在每一行的两头放置引号。

指定清单文件​

WITH LISTING IN 选项允许您将任何可能发生的编译时警告定向到文件。

当您在 UNIX™ 上工作时，下图展示如何将编译时警告记录在 /tmp/warn_file 中。

图: 在 UNIX 上记录编译时警告。

CREATE FUNCTION raise_prices(per_cent INT)
. . .
END FUNCTION
WITH LISTING IN '/tmp/warn_file'

当您在 Windows™ 上工作时，下图展示如何将编译时警告记录在 \tmp\listfile 中。

图: 在 Windows 上记录编译时警告。

CREATE FUNCTION raise_prices(per_cent INT)
. . .
END FUNCTION
WITH LISTING IN 'C:\tmp\listfile'

请始终记住在文件名称或路径名称两头放置单引号或双引号。

添加注释​

您可将注释添加到 SPL 例程的任何行，即使是空行也行。

要添加注释，请使用任一下列注释说明类型：

  • 在注释的左边放置双连字符（--）。
  • 将注释文本括在一对大括号之间（{  . .  . }）。
  • 在 C 类型的“斜杠和星号”注释指示符之间定界注释（/*  . .  . */）。

要添加多行注释，请执行下列操作之一：

  • 在每一行注释前放置双连字符
  • 将全部注释括在一对大括号之内。
  • 在注释的第一行的左边放置 /*，并在注释的最后一行的末尾放置 */。

以大括号作为注释指示符是 GBase 8s 对 SQL 语言的 ANSI/ISO 标准的扩展。在 SPL 例程中，全部三种注释类型也是有效的。

如果您使用大括号或 C 类型注释指示符来定界注释的文本，则开头的指示符必须与结尾的指示符是同一类型。

下图中的所有实例都是有效的注释。

图: 有效的注释实例。

SELECT * FROM customer -- Selects all columns and rows

SELECT * FROM customer
-- Selects all columns and rows
-- from the customer table

SELECT * FROM customer
{ Selects all columns and rows
from the customer table }

SELECT * FROM customer
/* 从 customer 表选择所有的列和行 */
重要

大括号（{ }）可用于定界注释，也用于定界集合中元素的列表。要确保解析器正确地识别注释的结束或集合中元素列表的结束，请在处理集合数据类型的 SPL 例程中为注释使用连字符（--）。

完整例程的示例​

下列 CREATE FUNCTION 语句创建读取客户地址的例程：

CREATE FUNCTION read_address       (lastname CHAR(15)) -- one argument
RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15),CHAR(2)
CHAR(5); -- 6 items

DEFINE p_lname,p_fname, p_city CHAR(15);
--define each routine variable
DEFINE p_add CHAR(20);
DEFINE p_state CHAR(2);
DEFINE p_zip CHAR(5);

SELECT fname, address1, city, state, zipcode
INTO p_fname, p_add, p_city, p_state, p_zip
FROM customer
WHERE lname = lastname;

RETURN p_fname, lastname, p_add, p_city, p_state, p_zip;
--6 items
END FUNCTION;

DOCUMENT 'This routine takes the last name of a customer as',
--brief description
'its only argument. It returns the full name and address',
'of the customer.'

WITH LISTING IN 'pathname' -- modify this pathname according
-- to the conventions that your operating system requires

-- compile-time warnings go here
; -- end of the routine read_address

在程序中创建 SPL 例程​

要使用 SQL API 来创建 SPL 例程，请将 CREATE PROCEDURE 或 CREATE FUNCTION 语句的文本放在文件中。请使用 CREATE PROCEDURE FROM 或 CREATE FUNCTION FROM 语句并引用那个文件来编译该例程。例如，要创建读取客户姓名的例程，您可使用诸如在前面的示例中的一个语句，并将它存储在文件中。如果将该文件命名为 read_add_source，则下列语句编译 read_address 例程：

CREATE PROCEDURE FROM 'read_add_source';

下列示例展示在 GBase 8s ESQL/C 程序中，前面的 SQL 语句是怎样的：

/* This program creates whatever routine is in *
* the file 'read_add_source'.
*/
#include <stdio.h>
EXEC SQL include sqlca;
EXEC SQL include sqlda;
EXEC SQL include datetime;
/* Program to create a routine from the pwd */

main()
{
EXEC SQL database play;
EXEC SQL create procedure from 'read_add_source';
}

在本地的或远程的数据库中删除例程​

在您创建 SPL 例程之后，您不可更改该例程体。相反，您需要删除该例程并重新创建它。然而，在您删除例程之前，请确保您在数据库之外的某个地方有它的文本的副本。

通常，请使用带有 SPL 过程名称的 DROP PROCEDURE 和带有 SPL 函数名称的 DROP FUNCTION，如下图所示。

图: DROP PROCEDURE 和 DROP FUNCTION。

DROP PROCEDURE raise_prices;
DROP FUNCTION read_address;
提示

您还可使用带有函数名称的 DROP PROCEDURE 来删除 SPL 函数。然而，推荐您使用仅带有过程名称的 DROP PROCEDURE，以及仅带有函数名称的 DROP FUNCTION。

如果数据库有同名称的其他例程（重载的例程），则您不可只通过它的例程名称来删除 SPL 例程。要删除已重载了的例程，您必须指定它的签名或它的特定的名称。下图展示您可能删除重载了的例程的两种方式。

图: 删除重载了的例程。

DROP FUNCTION calculate( a INT, b INT, c INT);
-- this is a signature
DROP SPECIFIC FUNCTION calc1;
-- this is a specific name

如果您不知道例程的类型（是函数还是过程），则您可使用 DROP ROUTINE 语句来删除它。DROP ROUTINE 对函数或过程都有效。DROP ROUTINE 还有 SPECIFIC 关键字，如下图所示。

图: DROP ROUTINE 语句。

DROP ROUTINE calculate;
DROP SPECIFIC ROUTINE calc1;

在您删除存储在远程数据库服务器上的 SPL 例程之前，请注意下列限制。仅当只用例程名称而不需它的参数，就足以标识该例程时，您才可以database@dbservername:owner.routinename 的形式使用完全限定的例程来删除 SPL 例程。

分布式操作中对数据类型的限制

如果 SPL 例程访问非本地的数据库服务器中的表，或调用 SPL 例程作为另一数据库服务器的数据库的 UDR，则该例程仅可有非 opaque 内建的数据类型作为它们的参数或返回的值。

然而，如果表或 UDR 驻留在同一 GBase 8s 示例的另一数据库上，则使用 SPL（或 GBase 8s 支持的外部语言）编写的例程的参数和返回的值可为内建的 opaque 数据类型 BLOB、BOOLEAN、CLOB 和 LVARCHAR。如果下列条件为真，则它们还可为 UDT 或 DISTINCT 数据类型：

  • 远程数据库与当前的数据库有相同的服务器。
  • 将 UDT 参数显式地强制转型为内建的数据类型。
  • DISTINCT 类型是基于内建的类型的，且被显式地强制转型为内建的类型。
  • 在所有参与的数据库中定义 SPL 例程和所有强制转型。

定义和使用变量​

您必须在例程体中定义在 SPL 例程中使用的任何变量，而不是在例程的参数列表中隐式地定义的变量。

在内存中保持变量的值；该变量不是数据库对象。因此，回滚事务不恢复 SPL 变量的值。

要在 SPL 例程中定义变量，请使用 DEFINE 语句。DEFINE 不是可执行语句。DEFINE 必须出现在 CREATE PROCEDURE 语句之后且任何其他语句之前。下图中的示例是所有合法的变量定义。

图: 变量定义。

DEFINE a INT;
DEFINE person person_t;
DEFINE GLOBAL gl_out INT DEFAULT 13;

要获取关于 DEFINE 的更多信息，请参阅《GBase 8s SQL 指南：语法》 中的描述。

SPL 变量有名称和数据类型。变量名称必须是有效的标识符，如 GBase 8s SQL 指南：语法 中“标识符”段中描述的那样。

声明本地变量​

您可定义变量为作用域中的本地的或全局的。本部分描述本地变量。在 SPL 例程中，本地变量：

  • 仅对于该 SPL 例程的持续时间是有效的
  • 每一次执行例程时，重置为它们的初始值或为用户传给该例程的值
  • 不可有缺省值

您可在任一下列数据类型上定义本地变量：

  • 内建的数据类型（除了 SERIAL、SERIAL8、BIGSERIAL、TEXT 或 BYTE 之外）
  • 在执行该 SPL 例程之前，在数据库中定义的任何扩展的数据类型（row 类型、opaque、distinct 或集合类型）

本地变量的作用域时在其中声明它的那个语句块。您可以不同的定义在该语句块之外使用相同的变量名称。

要获取关于定义全局变量的更多信息，请参阅 声明全局变量。

本地变量的作用域​

在定义本地变量的那个语句块内以及任何嵌套的语句块内，它是有效的，除非您在该语句块中重新定义该变量。

在系统中的 SPL 过程的开头，定义并初始化整数变量 x、y 和 z。

图: 定义和初始化变量。

CREATE PROCEDURE scope()
DEFINE x,y,z INT;
LET x = 5;
LET y = 10;
LET z = x + y; --z is 15
BEGIN
DEFINE x, q INT;
DEFINE z CHAR(5);
LET x = 100;
LET q = x + y;   -- q = 110
LET z = 'silly'; -- z receives a character value
END
LET y = x; -- y is now 5
LET x = z; -- z is now 15, not 'silly'
END PROCEDURE;

BEGIN 与 END 语句标记在其中定义整数变量 x 和 q 以及 CHAR 变量 z 的嵌套的语句块。在嵌套的块内，重新定义的变量 x 掩盖原始的变量 x。在标记该嵌套的块结束的 END 语句之后，可再次访问 x 的原始值。

声明内建的数据类型的变量​

声明为内建的 SQL 数据类型的变量可持有从那个内建的类型的列检索的值。您可将 SPL 变量声明为大部分内建的类型，除了 BIGSERIAL、SERIAL 和 SERIAL8 之外，如下图所示。

图: 内建的类型变量。

DEFINE x INT;
DEFINE y INT8;
DEFINE name CHAR(15);
DEFINE this_day DATETIME YEAR TO DAY;

您可声明适当的整数数据类型（诸如 BIGINT、INT 或 INT8）的 SPL 变量，来存储序列列或序列对象的值。

声明智能大对象的变量​

BLOB 或 CLOB 对象（或包含智能大对象的数据类型）的变量不包含该对象本身，而是指向该对象的指针。下图展示如何为 BLOB 和 CLOB 对象定义变量。

图: BLOB 或 CLOB 对象的变量。

DEFINE a_blob BLOB;
DEFINE b_clob CLOB;

声明简单大对象的变量​

简单大对象（TEXT 或 BYTE 对象）的变量不包含该对象本身，而是执行该对象的指针。当您对 TEXT 或 BYTE 数据类型定义变量时，您必须在数据类型之前使用关键字 REFERENCES，如下图所示。

图: 在数据类型之前使用 REFERENCES 关键字。

DEFINE t REFERENCES TEXT;
DEFINE b REFERENCES BYTE;

声明集合变量​

为了保持从数据库访存的集合，变量必须为类型 SET、MULTISET 或 LIST。

重要

必须将集合变量定义为本地变量。您不可将集合变量定义为全局变量。

SET、MULTISET 或 LIST 类型的变量是保存在 DEFINE 语句中命名的类型的集合的集合变量。下图展示如何定义 typed 集合变量。

图: 定义 typed 集合变量。

DEFINE a SET ( INT NOT NULL );

DEFINE b MULTISET ( ROW (  b1 INT,
b2 CHAR(50),
)  NOT NULL );

DEFINE c LIST ( SET (DECIMAL NOT NULL) NOT NULL);

您必须始终将集合变量的元素定义为 NOT NULL。在此示例中，定义变量 a 来保存非 NULL 整数的 SET；变量 b 保存非 NULL row 类型的 MULTISET；变量 c 保存非 NULL 十进制值的非 NULL 集合的 LIST。

在变量定义中，您可在任何组合或深度中嵌套复合的类型，来与存储在您的数据库中的数据类型相匹配。

您不可将一种类型的集合变量分配给另一类型的集合变量。例如，如果您将集合变量定义为 SET，则您不可将另一 MULTISET 或 LIST 类型的集合变量分配给它。

声明 row 类型变量​

Row 类型变量保存命名的和未命名的 row 类型的数据。您可定义命名的 row 变量或未命名的 row 变量。假设您定义如下图所示的命名的 row 类型。

图: 命名的和未命名的 row 变量。

CREATE ROW TYPE zip_t
(
z_code      CHAR(5),
z_suffix    CHAR(4)
);

CREATE ROW TYPE address_t
(
street      VARCHAR(20),
city        VARCHAR(20),
state       CHAR(2),
zip         zip_t
);

CREATE ROW TYPE employee_t
(
name        VARCHAR(30),
address     address_t
salary      INTEGER
);

CREATE TABLE employee OF TYPE employee_t;

如果您以命名的 row 类型的名称定义变量，则该变量仅可保存那种 row 类型的数据。在下图中，person 变量仅可保存 employee_t 类型的数据。

图: 定义 person 变量。

DEFINE person employee_t;

要定义保存在未命名的 row 类型中的数据的变量，请跟在 row 类型的字段之后使用 ROW 关键字，如下图所示。

图: 使用后跟 row 类型的字段的 ROW 关键字。

DEFINE manager ROW (name VARCHAR(30),
department VARCHAR(30),
salary INTEGER );

由于仅对未命名的 row 类型的结构等价进行类型检查，因此，以未命名的 row 类型定义的变量可保存任何未命名的 row 类型的数据，其有相同的字段数和相同的类型定义。因此，变量 manager 可保存下图中任何 row 类型的数据。

图: 未命名的 row 类型。

ROW ( name VARCHAR(30),
department VARCHAR(30),
salary INTEGER );

ROW ( french VARCHAR(30),
spanish VARCHAR(30),
number INTEGER );

ROW ( title VARCHAR(30),
musician VARCHAR(30),
price INTEGER );
重要

在您使用 row 类型变量之前，您必须使用 LET 语句或 SELECTINTO 语句来初始化该 row 变量。

声明 opaque 类型和 distinct 类型变量​

Opaque 类型变量保存从 opaque 数据类型检索的数据。Distinct 类型变量保存从 distinct 数据类型检索的数据。如果您以 opaque 数据类型或 distinct 数据类型定义变量，则该变量仅可保存那种类型的数据。

如果您定义名为 point 的 opaque 数据类型，和名为 centerpoint 的 distinct 数据类型，则您可定义 SPL 变量来保存这两类数据，如下图所示。

图: 定义 SPL 变量来保存 opaque 和 distinct 数据类型。

DEFINE a point;
DEFINE b centerpoint;

变量 a 仅可保存类型 point 的数据，b 仅可保存类型 centerpoint 的数据。

使用 LIKE 子句来声明列数据的变量​

如果您使用 LIKE 子句，则数据库服务器定义有相同数据类型的变量作为表或视图中的列。

如果该列包含集合、row 类型或嵌套的复合类型，则该变量具有在该列中定义的复合的或嵌套的复合类型。

在下图中，变量 loc1 定义 image 表中 locations 列的数据类型。

图: 为 image 表中的 locations 列定义 loc1 数据类型。

DEFINE loc1 LIKE image.locations;

声明 PROCEDURE 类型变量​

在 SPL 例程中，您可定义类型 PROCEDURE 的变量，并将现有的 SPL 例程或外部例程的名称分配给该变量。定义 PROCEDURE 类型的变量指示该变量是对用户定义的例程的调用，而不是对同一名称的内建例程的调用。

例如，下图中的语句定义 length 为一个 SPL 过程或 SPL 函数，不作为内建的 LENGTH 函数。

图: 定义 length 作为 SPL 过程。

DEFINE length PROCEDURE;
LET x = length( a,b,c );

此定义在该语句块的作用域内禁用内建的 LENGTH 函数。如果您已以名称 LENGTH 创建了 SPL 或外部例程，则您可使用这样的定义。

由于 GBase 8s 支持例程重载，因此，您可以相同的名称定义多个 SPL 例程或外部例程。如果您从 SPL 例程调用任何例程，则 GBase 8s 基于指定的参数和例程确定规则，确定使用哪个例程。要获取关于例程重载和例程确定的信息，请参阅《GBase 8s 用户定义的例程和数据类型开发者指南》。

提示

如果您以相同的名称创建 SPL 例程作为聚集函数（SUM、MAX、MIN、AVG、COUNT）或使用名称 extend，则您必须以所有者名称来限定该例程。

带有变量的下标​

您可随同 CHAR、VARCHAR、NCHAR、NVARCHAR、BYTE 或 TEXT 数据类型的变量使用下标。下标指示您想要在变量内使用的起始的和终止的字符位置。

下标必须始终为常量。您不可使用变量作为下标。下图展示如何随同 CHAR(15) 变量使用下标。

图: 带有 CHAR(15) 变量的下标。

DEFINE name CHAR(15);
LET name[4,7] = 'Ream';
SELECT fname[1,3] INTO name[1,3] FROM customer
WHERE lname = 'Ream';

在此示例中，将客户的姓置于 name 的位置 4 与 7 之间。将客户的名的前三个字符检索到 name 的位置 1 至 3 内。由两个下标定界的变量的该部分称为子字符串。

变量与关键字歧义​

如果您声明的变量的名字是 SQL 关键字，则可发生歧义。下列标识符的规则帮助您避免 SPL 变量、SPL 例程名称和内建的函数名称的歧义：

  • 定义了的变量优先级最高。
  • 以 DEFINE 语句中的 PROCEDURE 关键字定义的例程优先于 SQL 函数。
  • SQL 函数优先于那些存在但未以 DEFINE 语句中的 PROCEDURE 关键字标识的 SPL 例程。

通常，请避免为变量的名称使用 ANSI 保留字。例如，您不可以名称 count 或 max 定义变量，因为它们是聚集函数的名称。要了解您应避免用作变量名称的保留的关键字列表，请参阅《GBase 8s SQL 指南：语法》中的“标识符”段。

要获取关于 SPL 例程名称与 SQL 函数名称之间的歧义的信息，请参阅《GBase 8s SQL 指南：语法》。

变量和列名称​

如果您为 SPL 变量使用一个您为列名称使用的同样的标识符，则数据库服务器假定该标识符的每一实例都是变量。请以表名称限定列名称，使用点符号表示法，以便将标识符用作列名称。

在下图中的 SELECT 语句中，customer.lname 是列名称，lname 是变量名称。

图: SELECT 语句中的列名称和变量名称。

CREATE PROCEDURE table_test()

DEFINE lname CHAR(15);
LET lname = 'Miller';

SELECT customer.lname INTO lname FROM customer
WHERE customer_num = 502;
. . .
END PROCEDURE;
变量和 SQL 函数​

如果您为 SPL 变量使用与为 SQL 函数一样的标识符，则数据库服务器假定该表达式的每一实例都是变量，并不允许使用该 SQL 函数。在定义该变量的代码块内，您不可使用该 SQL 函数。下图中的示例展示在其中定义名为 user 的变量的 SPL 过程内的块。此定义不允许在 BEGIN END 块中使用 USER 函数。

图: 不允许在 BEGIN END 块中使用 USER 函数的过程。

CREATE PROCEDURE user_test()
DEFINE name CHAR(10);
DEFINE name2 CHAR(10);
LET name = user; -- the SQL function

BEGIN
DEFINE user CHAR(15); -- disables user function
LET user = 'Miller';
LET name = user; -- assigns 'Miller' to variable name
END
. . .
LET name2 = user; -- SQL function again

声明全局变量​

全局变量将它的值存储在内存中，其他 SPL 例程可用，由相同的用户会话运行在同一数据库上。全局变量有下列特征：

  • 它需要缺省值。
  • 可在任何 SPL 例程中使用它，虽然必须在使用它的每一例程中定义它。
  • 它将它的值从一个 SPL 例程带到另一个，直到会话结束为止。
限制

您不可将集合变量定义为全局变量。

下图展示分享一个全局变量的两个 SPL 函数。

图: 分享一个全局变量的两个 SPL 函数。

CREATE FUNCTION func1()  RETURNING INT;
DEFINE GLOBAL gvar INT DEFAULT 2;
LET gvar = gvar + 1;
RETURN gvar;
END FUNCTION;

CREATE FUNCTION func2()  RETURNING INT;
DEFINE GLOBAL gvar INT DEFAULT 5;
LET gvar = gvar + 1;
RETURN gvar;
END FUNCTION;

虽然您必须定义带有缺省值的全局变量，但仅在您首次使用它时，将变量设置为缺省值。如果您以给定的顺序在下图中执行这两个函数，则 gvar的值将为 4。

图: 全局变量缺省值。

EXECUTE FUNCTION func1();
EXECUTE FUNCTION func2();

但是，如果您以相反的顺序执行函数，如下图所示，则 gvar 的值将为 7。

图: 全局变量缺省值。

EXECUTE FUNCTION func2();
EXECUTE FUNCTION func1();

要了解更多信息，请参阅 执行例程。

赋值给变量​

在 SPL 例程内，请使用 LET 语句将值分配给您已定义的变量。

如果您未赋值给变量，或通过传递给例程的参数，或通过 LET 语句，则该变量有未定义的值。

未定义的值与 NULL 值不同。如果您尝试以 SPL 例程内未定义的值使用变量，则会收到错误。

您可以下列任一方式赋值给例程变量：

  • 使用 LET 语句。
  • 使用 SELECT INTO 语句。
  • 将 CALL 语句与带有 RETURNING 子句的过程一起使用。
  • 使用 EXECUTE PROCEDURE INTO 或 EXECUTE FUNCTION INTO 语句。

LET 语句​

以 LET 语句，您可以等号（=）和有效的表达式或函数名称来使用一个或多个变量名称。下图中的每一示例都是有效的 LET 语句。

图: 有效的 LET 语句。

LET a = 5;
LET b = 6; LET c = 10;
LET a,b = 10,c+d;
LET a,b = (SELECT cola,colb
FROM tab1 WHERE cola=10);
LET d = func1(x,y);

GBase 8s 允许您将值分配给 opaque 类型变量、row 类型变量，或 row 类型的字段。您还可将外部函数或另一 SPL 函数的值返回到 SPL 变量。

假设您定义命名的 row 类型 zip_t 和 address_t，如 图 1 所示。每当您定义 row 类型变量时，您必须在可使用它之前初始化该变量。下图展示您可能如何定义和初始化 row 类型变量。您可使用任何 row 类型值来初始化该变量。

图: 定义和初始化 row 类型变量。

DEFINE a address_t;
LET a = ROW ('A Street', 'Nowhere', 'AA',
ROW(NULL, NULL))::address_t

在您定义并初始化 row 类型变量之后，您可编写下图所示的 LET 语句。

图: 编写 LET 语句。

LET a.zip.z_code = 32601;
LET a.zip.z_suffix = 4555;
-- Assign values to the fields of address_t
提示

请以 variable.field or variable.field.field 的形式使用点符号表示法来访问 row 类型的字段，如 处理 row 类型数据 描述的那样。

假设您定义 opaque-type point，其包含定义二维点的两个值，且该值的文本表示为 '(x,y)'。您还可能有计算圆的周长的函数 circum()，给定的点 '(x,y)' 和半径 r。

如果您定义以一点为圆心的 opaque 类型 center，以及计算圆的周长的函数 circum()，基于点和半径，您可为每一变量编写变量声明。在下图中，c 是一个 opaque 类型变量，d 保存外部函数 circum() 返回的值。

图: 编写变量声明。

DEFINE c point;
DEFINE r REAL;
DEFINE d REAL;

LET c = '(29.9,1.0)' ;
-- Assign a value to an opaque type variable

LET d = circum( c, r );
-- Assign a value returned from circum()

GBase 8s SQL 指南：语法 详细地描述 LET 语句的语法。

赋值给变量的其他方式​

您可使用 SELECT 语句来从数据库访存一个值，并直接地将它分配给变量，如下图所示。

图: 从数据库访存一个值，并直接地将它分配给变量。

SELECT fname, lname INTO a, b FROM customer
WHERE customer_num = 101

请使用 CALL 或 EXECUTE PROCEDURE 语句来将由 SPL 函数或外部函数返回的值分配给一个或多个 SPL 变量。您可能使用下图中的一个语句来将来自 SPL 函数 read_address 的全名和地址返回到指定的 SPL 变量内。

图: 返回来自 SPL 函数的全名和地址。

EXECUTE FUNCTION read_address('Smith')
INTO p_fname, p_lname, p_add, p_city, p_state,
p_zip;

CALL read_address('Smith')
RETURNING p_fname, p_lname, p_add, p_city,
p_state, p_zip;

SPL 例程中的表达式​

您可在 SPL 例程中使用任何 SQL 表达式，除了聚集表达式以外。GBase 8s SQL 指南：语法 提供 SQL 表达式的完整语法与描述。

下列示例包含 SQL 表达式：

var1
var1 + var2 + 5
read_address('Miller')
read_address(lastname = 'Miller')
get_duedate(acct_num) + 10 UNITS DAY
fname[1,5] || ''|| lname '(415)' || get_phonenum(cust_name)

编写语句块​

每个 SPL 例程至少有一个语句块，它是在 CREATE 语句与 END 语句之间的一组 SQL 和 SPL 语句。您可在语句块内使用任何 SPL 语句或任何允许的 SQL 语句。要了解在 SPL 语句块内不允许使用的 SQL 语句的列表，请参阅《GBase 8s SQL 指南：语法》 中语句块段的描述。

隐式的和显式的语句块​

在 SPL 例程中，隐式的语句块从 CREATE 语句的结尾扩展到 END 语句的开头。您还可定义显式的语句块，它以 BEGIN 语句开头并以 END 语句结尾，如下图所示。

图: 显式的语句块。

BEGIN
DEFINE distance INT;
LET distance = 2;
END

显式的语句块允许您定义仅在语句块内有效的变量或处理。例如，您可定义或重新定义变量，或以不同的方式处理异常，仅对于显式的语句块的作用域。

下图中的 SPL 函数有一个显式的语句块，它重新定义在隐式的块中定义的变量。

图: 重新定义在隐式的块中定义的变量的显式的语句块。

CREATE FUNCTION block_demo()
RETURNING INT;
DEFINE distance INT;
LET distance = 37;
BEGIN
DEFINE distance INT;
LET distance = 2;
END
RETURN distance;

END FUNCTION;

在此示例中，隐式的语句块定义变量 distance 并赋值 37。显式的语句块定义名为 distance 的不同的变量，并赋值 2。然而，RETURN 语句返回存储在第一个 distance 变量中的值，即 37。

FOREACH 循环​

FOREACH 循环定义游标，指向一组中的一项的特定的=标识符，或为一组行，或为集合中的元素。

FOREACH 循环声明并打开游标，从数据库访存行，处理该组中的每一项，然后关闭游标。如果 SELECT、EXECUTE PROCEDURE 或 EXECUTE FUNCTION 语句可能返回多行，则您必须声明游标。在您声明游标之后，将 SELECT、EXECUTE PROCEDURE 或 EXECUTE FUNCTION 语句放置在其内。

返回一组行的 SPL 例程称为游标例程，因为您必须使用游标来访问它返回的数据。不返回值、返回单个值或任何其他值的 SPL 例程不需要游标，称为无游标例程。FOREACH 循环声明并打开游标，从数据库访存行或集合，处理该组中的每一项，然后关闭游标。如果 SELECT、EXECUTE PROCEDURE 或 EXECUTE FUNCTION 语句可能返回多个行或集合，则您必须声明游标。在您声明游标之后，请将 SELECT、EXECUTE PROCEDURE 或 EXECUTE FUNCTION 语句放置其内。

在 FOREACH 循环中，您可使用 EXECUTE FUNCTION 或 SELECT INTO 语句来执行为迭代函数的外部函数。

FOREACH 循环定义游标​

FOREACH 循环以 FOREACH 关键字开始，并以 END FOREACH 结束。在 FOREACH 与 END FOREACH 之间，您可声明游标或使用 EXECUTE PROCEDURE 或 EXECUTE FUNCTION。下图中的两个示例展示 FOREACH 循环的结构。

图: FOREACH 循环的结构。

FOREACH cursor FOR
SELECT column INTO variable FROM table
. . .
END FOREACH;

FOREACH
EXECUTE FUNCTION name() INTO variable;
END FOREACH;

下图创建使用 FOREACH 循环的例程来在 employee 表上操作。

图: 对 employee 表操作的 FROEACH 循环。

CREATE_PROCEDURE increase_by_pct( pct INTEGER )
DEFINE s INTEGER;

FOREACH sal_cursor FOR
SELECT salary INTO s FROM employee
WHERE salary > 35000
LET s = s + s * ( pct/100 );
UPDATE employee SET salary = s
WHERE CURRENT OF sal_cursor;
END FOREACH;

END PROCEDURE;

前图中的例程执行 FOREACH 内的这些任务：

  • 声明游标
  • 一次从 employee 表选择一个 salary 值
  • 按百分率提高 salary
  • 以新的 salary 更新 employee
  • 访存下一个 salary 值

将 SELECT 语句放置在游标内，因为它返回表中所有大于 35000 的薪酬。

UPDATE 语句中的 WHERE CURRENT OF 子句仅更新该游标当前定位在其上的行，并在当前行上设置更新游标。更新游标在该行上放置更新锁，以便于其他用户不可更新该行，直到您的更新发生为止。

如果 FOREACH 循环内的 UPDATE 或 DELETE 语句使用 WHERE CURRENT OF 子句，则 SPL 例程将自动地设置更新游标。如果您使用 WHERE CURRENT OF，则必须显式地引用 FOREACH 语句内的游标。如果您正在使用更新游标，则可在 FOREACH 语句之前添加 BEGIN WORK 语句，并在 END FOREACH 之后添加 COMMIT WORK 语句，如下图所示。

图: 自动地设置更新游标。

BEGIN WORK;
FOREACH sal_cursor FOR
SELECT salary INTO s FROM employee WHERE salary > 35000;
LET s = s + s * ( pct/100 );
UPDATE employee SET salary = s WHERE CURRENT OF sal_cursor
END FOREACH;
COMMIT WORK;

对于前图中 FOREACH 循环的每一迭代，需要新锁（如果您使用行级别锁定的话）。在 FOREACH 循环的最后迭代之后，COMMIT WORK 语句释放所有的锁（并将所有更新了的行作为单个事务提交）。

要在循环的每一迭代之后提交更新了的行，您必须打开游标 WITH HOLD，并在 FOREACH 循环内包括 BEGIN WORK 和 COMMIT WORK 语句，如下列 SPL 例程那样。

图: 在循环的每一迭代之后提交更新了的行。

CREATE PROCEDURE serial_update();
DEFINE p_col2 INT;
DEFINE i INT;
LET i = 1;
FOREACH cur_su WITH HOLD FOR
SELECT col2 INTO p_col2 FROM customer WHERE 1=1
BEGIN WORK;
UPDATE customer SET customer_num = p_col2 WHERE CURRENT OF cur_su;
COMMIT WORK;
LET i = i + 1;
END FOREACH;
END PROCEDURE;

SPL 例程 serial_update() 提交每一行作为分开的事务。

对 FOREACH 循环的限制​

在 FOREACH 循环内，SELECT 查询必须在更改该 SELECT 游标的数据集的任何 DELETE、INSERT 或 UPDATE 操作之前执行完成。确保 SELECT 查询完成的一种方式是，在 SELECT 语句中使用 ORDER BY 子句。ORDER BY 子句在该列上创建索引，并通过在同一 FOREACH 循环中更改 SELECT 语句的查询结果的 UPDATE、INSERT、DELETE 语句来防止导致的错误。

IF - ELIF - ELSE 结构​

下列 SPL 例程使用 IF - ELIF - ELSE 结构来比较该例程接受的两个参数。

图: 比较两个参数的 IF - ELIF - ELSE 结构。

CREATE FUNCTION str_compare( str1 CHAR(20), str2 CHAR(20))
RETURNING INTEGER;

DEFINE result INTEGER;

IF str1 > str2 THEN
LET result = 1;
ELIF str2 > str1 THEN
LET result = -1;
ELSE
LET result = 0;
END IF
RETURN result;
END FUNCTION;

假设您以下图所示的列定义名为 manager 的表。

图: 定义 manager 表。

CREATE TABLE manager
(
mgr_name   VARCHAR(30),
department VARCHAR(12),
dept_no    SMALLINT,
direct_reports SET( VARCHAR(30) NOT NULL ),
projects LIST( ROW ( pro_name VARCHAR(15),
pro_members SET( VARCHAR(20) NOT NULL ) )
NOT NULL),
salary     INTEGER,
);

下列 SPL 例程使用 IF - ELIF - ELSE 结构来检查 direct_reports 列中 SET 中元素的数目，并基于该结果来调用不同的外部例程。

图: 检查 SET 中元素数的 IF - ELIF - ELSE 结构。

CREATE FUNCTION checklist( d SMALLINT )
RETURNING VARCHAR(30), VARCHAR(12), INTEGER;

DEFINE name VARCHAR(30);
DEFINE dept VARCHAR(12);
DEFINE num INTEGER;

SELECT mgr_name, department,
CARDINALITY(direct_reports)
FROM manager INTO name, dept, num
WHERE dept_no = d;
IF num > 20 THEN
EXECUTE FUNCTION add_mgr(dept);
ELIF num = 0 THEN
EXECUTE FUNCTION del_mgr(dept);
ELSE
RETURN name, dept, num;
END IF;

END FUNCTION;

cardinality() 函数计数集合包含的元素数。要获取更多信息，请参阅 基数函数。

SPL 例程中的 IF - ELIF - ELSE 结构有至多下列四个部分：

  • IF THEN 条件

    如果跟在 IF 语句之后的该条件为 TRUE，则例程执行 IF 块中的语句。如果该条件为假，则例程对 ELIF 条件求值。

    IF 语句中的表达式可为任何有效的条件，如 GBase 8s SQL 指南：语法 的 Condition 段描述的那样。要了解 IF 语句的完整语法和详细的讨论，请参阅《GBase 8s SQL 指南：语法》。

  • 一个或多个 ELIF 条件（可选的）

    仅当 IF 条件为假时，例程才对 ELIF 条件求值。如果 ELIF 条件为真，则例程执行 ELIF 块中的语句。如果 ELIF 条件为假，则例程或对下一个 ELIF 块求值，或执行 ELSE 语句。

  • ELSE 条件（可选的）

    如果 IF 条件和所有 ELIF 条件都为假，则例程执行 ELSE 块中的语句。

  • END IF 语句

    END IF 语句结束该语句块。

添加 WHILE 和 FOR 循环​

WHILE 与 FOR 语句都可在 SPL 例程中创建执行循环。WHILE 循环以 WHILE condition 开始，只要条件为真就执行语句块，并以 END WHILE 结束。

下图展示有效的 WHILE 条件。只要在 WHILE 语句中指定的条件为真，例程就执行 WHILE 循环。

图: 只要在 WHILE 语句中指定的条件为真，例程就执行 WHILE 循环。

CREATE PROCEDURE test_rows( num INT )

DEFINE i INTEGER;
LET i = 1;

WHILE i < num
INSERT INTO table1 (numbers) VALUES (i);
LET i = i + 1;
END WHILE;

END PROCEDURE;

前图中的 SPL 例程接受整数作为参数，然后在它每一次执行 WHILE 循环时，就将整数值插入到 table1 的 numbers 列内。插入的值从 1 开始，且增大到 num - 1。

请当心，不要创建无限的循环，如下图所示。

图: 接受整数为参数，然后将整数值插入到 numbers 列的例程。

CREATE PROCEDURE endless_loop()

DEFINE i INTEGER;
LET i = 1;
WHILE ( 1 = 1 )            -- don't do this!
LET i = i + 1;
INSERT INTO table1 VALUES (i);
END WHILE;

END PROCEDURE;

FOR 循环从 FOR 语句扩展到 END FOR 语句，并执行在 FOR 语句中定义的指定次数的迭代。下图展示在 FOR 循环中定义迭代的几种方式。

对于 FOR 循环的每一迭代，重置迭代变量（在后面的示例中声明为 i），并以该变量的新值执行该循环内的语句。

图: 定义 FOR 循环中的迭代。

FOR i = 1 TO 10
. . .
END FOR;

FOR i = 1 TO 10 STEP 2
. . .
END FOR;

FOR i IN (2,4,8,14,22,32)
. . .
END FOR;

FOR i IN (1 TO 20 STEP 5, 20 to 1 STEP -5, 1,2,3,4,5)
. . .
END FOR:

在第一个示例中，只要 i 介于 1 之间 10，包括 1 与 10，该 SPL 过程就执行 FOR 循环。在第二个示例中，i 从 1 到 3、5、7，等等递进，但从不超过 10。第三个示例检查 i 是否在定义了的值集之内。在第四个示例中，当 i 为 1、6、11、16、20、15、10、5、1、2、3、4 或 5 时，该 SPL 过程执行循环——换言之，执行循环 13 次。

提示

WHILE 循环与 FOR 循环之间的主要差异是，FOR 循环保证会结束，但 WHILE 循环不然。FOR 语句指定循环执行的确切次数，除非语句导致例程退出该循环。使用 WHILE，可能创建无限的循环。

退出循环​

在没有标签的 FOR、FOREACH、LOOP 或 WHILE 循环中，您可使用 CONTINUE 或 EXIT 语句来控制循环的执行。

  • CONTINUE 导致例程跳过该循环的剩余语句，并移至 FOR、LOOP 或 WHILE 语句的下一迭代。
  • EXIT 终止该循环，并导致例程继续执行跟在 END FOR、END LOOP 或 END WHILE 关键字之后的第一个语句。

请记住，当 EXIT 出现在为嵌套循环语句的最内层循环的 FOREACH 语句内时，它必须后跟 FOREACH 关键字。当 EXIT 出现在在 FOR、LOOP 或 WHILE 语句内时，它可不紧跟关键字出现，但如果您指定一个关键字，该关键字与从其发出了 EXIT 语句的循环语句不相匹配，则发出错误。如果 EXIT 出现在循环语句的上下文之外，则也发出错误。

要获取关于 SPL 例程中的循环的更多信息，包括带标签的循环，请参阅《GBase 8s SQL 指南：语法》。

下图展示在 FOR 循环内的 CONTINUE 和 EXIT 的示例。

图: FOR 循环内的 CONTINUE 和 EXIT 的示例。

FOR i = 1 TO 10
IF i = 5 THEN
CONTINUE FOR;
. . .
ELIF i = 8 THEN
EXIT FOR;
END IF;

END FOR;
提示

您可使用 CONTINUE 和 EXIT 来提升 SPL 例程的性能，以免执行不必要的循环。

从 SPL 函数返回值​

SPL 函数可返回一个或多个值。要是您的 SPL 函数返回值，需要包括下列两个部分：

  1. 在指定要返回的值的数目及其数据类型的 CREATE PROCEDURE 或 CREATE FUNCTION 语句中编写 RETURNING 子句。
  2. 在函数体内，输入显式地返回值的 RETURN 语句。
提示

您可以返回值的 CREATE PROCEDURE 语句来定义例程，但在那种情况下，该例程实际上是函数。但例程返回值时，推荐您使用 CREATE FUNCTION 语句。

在您（以 RETURNING 语句）定义返回子句之后，SPL 函数可返回那些与指定的数目和数据类型相匹配的值，或根本不返回值。如果您指定返回子句，且 SPL 例程未返回实际的值，则仍将它视为函数。在那种情况下，例程为在返回子句中定义的每一值都返回一个 NULL 值。

SPL 函数可返回变量、表达式，或另一函数调用的结果。如果 SPL 函数返回变量，则该函数必须首先通过下列方法之一赋值给该变量：

  • LET 语句
  • 缺省值
  • SELECT 语句
  • 将值传至该变量内的另一函数

SPL 函数返回的每一值最长可为 32 KB。

重要

SPL 函数的返回值必须为特定的数据类型。您不可指定类属行或类属集合数据类型作为返回类型。

返回单个值​

下图展示 SPL 函数可如何返回单个值。

图: 返回单个值的 SPL 函数。

CREATE FUNCTION increase_by_pct(amt DECIMAL, pct DECIMAL)
RETURNING DECIMAL;

DEFINE result DECIMAL;

LET result = amt + amt * (pct/100);

RETURN result;

END FUNCTION;

increase_by_pct 函数收到两个 DECIMAL 值的参数，一个为要增加的数量，一个为要增加的百分比。指定该函数的返回子句将返回一个 DECIMAL 值。RETURN 语句返回存储在 result 中的 DECIMAL 值。

返回多个值​

SPL 函数可从表的单个行返回多个值。下图展示从表的单个行返回两个列值的 SPL 函数。

图: 从表的单个行返回两个列值的 SPL 函数。

CREATE FUNCTION birth_date( num INTEGER )
RETURNING VARCHAR(30), DATE;

DEFINE n VARCHAR(30);
DEFINE b DATE;

SELECT name, bdate INTO n, b FROM emp_tab
WHERE emp_no = num;
RETURN n, b;

END FUNCTION;

该函数从 emp_tab 表的一行将两个值（名和生日）返回给调用的例程。在此情况下，必须准备调用的例程来处理返回的 VARCHAR 和 DATE 值。

下图展示从多行返回多个值的 SPL 函数。

图: 从多行返回多个值的 SPL 函数。

CREATE FUNCTION birth_date_2( num INTEGER )
RETURNING VARCHAR(30), DATE;
DEFINE n VARCHAR(30);
DEFINE b DATE;
FOREACH cursor1 FOR
SELECT name, bdate INTO n, b FROM emp_tab
WHERE emp_no > num
RETURN n, b WITH RESUME;
END FOREACH;
END FUNCTION;

在前图中，SELECT 语句从其员工编号大于用户输入的编号的行集访存两个值。满足该条件的行集可能包含一行、多行，或零行。由于 SELECT 语句可返回多行，因此将它放置在游标内。

提示

当 SPL 例程内的语句未返回行时，为对应的 SPL 变量赋值 NULL。

RETURN 语句使用 WITH RESUME 关键字。当执行 RETURN WITH RESUME 时，将控制返回到调用的例程。但在下一次（通过 FETCH 或通过调用的例程中的游标的下一迭代）调用该 SPL 函数时，SPL 函数中的所有变量保持它们的相同的值，并从紧跟在 RETURN WITH RESUME 语句之后的语句继续执行。

如果您的 SPL 例程返回多个值，则调用的例程必须能够通过游标或循环来处理多个值，如下：

  • 如果调用的例程为 SPL 例程，则它需要 FOREACH 循环。
  • 如果调用的例程为 GBase 8s ESQL/C 程序，则它需要以 DECLARE 语句声明的游标。
  • 如果调用的例程为外部的例程，则它需要与编写该例程的语言相适应的游标或循环。
重要

由 UDR 从本地服务器的外部数据库返回的值必须为内建的数据类型，或 UDT 显式地强制转型为内建的类型，或基于内建的类型的 DISTINCT 类型并显式地强制转型为内建的类型。此外，您必须定义 UDR 和参与的数据库中的所有强制转型。

下列是您可跨数据库执行的 SQL 操作的示例：

database db1;
create table ltab1(lcol1 integer, lcol2 boolean, lcol3 lvarchar);
insert into ltab1 values(1, 't', "test string 1");

database db2;
create table rtab1(r1col1 boolean, r1col2 blob, r1col3 integer)
put r1col2 in (sbsp);
create table rtab2(r2col1 lvarchar, r2col2 clob) put r2col2 in (sbsp);
create table rtab3(r3col1 integer, r3col2 boolean,
r3col3 lvarchar, r3col4 circle);

create view rvw1 as select  * from rtab3;

（该示例为跨数据库 Insert。）

database db1;
create view lvw1 as select * from db2:rtab2;
insert into db2:rtab1 values('t',
filetoblob('blobfile', 'client', 'db2:rtab1', 'r1col2'), 100);
insert into db2:rtab2 values("inserted directly to rtab2",
filetoclob('clobfile', 'client', 'db2:rtab2', 'r2col2'));
insert into db2:rtab3 (r3col1, r3col2, r3col3)
select lcol1, lcol2, lcol3 from ltab1;
insert into db2:rvw1 values(200, 'f', "inserted via rvw1");
insert into lvw1 values ("inserted via lvw1", NULL);

处理 row 类型数据​

在 SPL 例程中，您可使用命名的 ROW 类型和未命名的 ROW 类型作为参数定义、参数、变量定义和返回值。要获取关于如何在 SPL 中声明 ROW 变量的信息，请参阅 声明 row 类型变量。

下图定义 row 类型 salary_t 和 emp_info 表，它们是本部分使用的示例。

图: 定义 row 类型 salary_t 和 emp_info 表

CREATE ROW TYPE salary_t(base MONEY(9,2), bonus MONEY(9,2))
CREATE TABLE emp_info (emp_name VARCHAR(30), salary salary_t);

emp_info 表有员工姓名和薪酬信息的列。

点符号表示法的优先顺序​

以 GBase 8s，SPL 例程中 SQL 语句中使用点符号表示法（如在 proj.name）被解释为有三种含义之一，优先顺序如下：

  1. variable.field
  2. column.field
  3. table.column

换言之，首先将表达式 proj.name 求值为 variable.field。如果例程未找到变量 proj，则它将该表达式求值为 column.field。如果例程未找到列 proj，则它将该表达式求值为 table.column。（如果不可将名称解析为数据库中对象的标识符，或在 SPL 例程中声明了的变量或字段，则返回错误。）

更新 row 类型表达式​

从 SPL 例程内，您可使用 ROW 变量来更新 row 类型表达式。下图展示当员工的基本薪酬按某一百分比增长时，用于更新 emp_info 表的 SPL 过程 emp_raise。

图: 用于更新 emp_info 表的 SPL 过程。

CREATE PROCEDURE emp_raise( name VARCHAR(30),
pct DECIMAL(3,2) )

DEFINE row_var salary_t;

SELECT salary INTO row_var FROM emp_info
WHERE emp_name = name;

LET row_var.base = row_var.base * pct;

UPDATE emp_info SET salary = row_var
WHERE emp_name = name;
END PROCEDURE;

SELECT 语句将来自 emp_info 表的 salary 列的行选择到 ROW 变量 row_var 内。

emp_raise 过程使用 SPL 点符号表示法来直接地访问变量 row_var 的 base 字段。在此情况下，点符号表示法意味着 variable.field。emp_raise过程重新计算 row_var.base 的值作为 (row_var.base * pct)。然后，该过程以新的 row_var 值来更新 emp_info 表的 salary 列。

重要

在可设置或引用 row 类型变量字段之前，必须将它初始化为行。您可以 SELECT INTO 语句或 LET 语句初始化 row 类型变量。

处理集合​

集合是同一数据类型的一组元素，诸如 SET、MULTISET 或 LIST。

表可能包含集合，存储集合作为列的内容，或作为列内 ROW 类型的字段。集合可为简单的或嵌套的。简单的集合是内建的、opaque 或 distinct 数据类型的 SET、MULTISET 或 LIST。嵌套的集合是包含其他集合的集合。

集合数据类型​

本章节的下列部分凭借几个不同的示例来展示您可如何在 SPL 程序中操纵集合。

在 SPL 程序中处理集合的基本内容是使用 numbers 表来说明的，如下图所示。

图: 在 SPL 程序中处理集合。

CREATE TABLE numbers
(
id INTEGER PRIMARY KEY,
primes       SET( INTEGER NOT NULL ),
evens        LIST( INTEGER NOT NULL ),
twin_primes  LIST( SET( INTEGER NOT NULL )
NOT NULL )

primes 和 evens 列保存简单的集合。twin_primes 列保存嵌套的集合，SET 的 LIST。（双素数是一对相差 2 的连续素数，诸如 5 和 7，或 11 和 13。）设计 twin_primes 列以允许您输入这样的值对。

本章节中的一些示例使用下图中的 polygons 表，来说明如何操纵集合。polygons 表包含集合来表示两维图形数据。例如，假设您定义名为point 的 opaque 数据类型，其有表示两维点的 x 和 y 坐标的两个双精度值，其坐标可能表示为 '1.0, 3.0'。使用 point 数据类型，您可创建包含一系列定义多边形的点的表。

图: 操纵集合。

CREATE OPAQUE TYPE point ( INTERNALLENGTH = 8);

CREATE TABLE polygons
(
id          INTEGER PRIMARY KEY,
definition  SET( point NOT NULL )
);

polygons 表中的 definition 列包含简单的集合，point 值的 SET。

准备集合数据类型​

在您可访问和处理简单的或嵌套的集合的个别元素之前，您必须执行下列任务：

  • 声明集合变量来保存该集合。
  • 声明元素变量来保存集合的个别元素。
  • 将集合从数据库选择至集合变量内。

在您做了这些初始的步骤之后，您可将元素插入到集合内，或选择或处理已在集合中的元素。

在下列部分中，使用 numbers 表为示例，说明每一步骤。

提示

您可在任何 SPL 例程中处理集合。

声明集合变量​

在您可从数据库将集合检索至 SPL 例程内之前，您必须声明集合变量。下图展示如何声明集合变量来从 numbers 表检索 primes 列。

图: 声明集合变量。

DEFINE p_coll SET( INTEGER NOT NULL );

DEFINE 语句声明集合变量 p_coll，其类型与存储在 primes 列中的集合的数据类型相匹配。

声明元素变量​

在您声明集合变量之后，请声明元素变量来保存该集合的个别元素。元素变量的数据类型必须与集合元素的数据类型相匹配。

例如，要保存 primes 列中 SET 的元素，请使用诸如下图所示的一种元素变量声明。

图: 元素变量声明。

DEFINE p INTEGER;

要声明保存 twin_primes 列的元素的变量，其保存嵌套的集合，请使用诸如下图所示的一种变量声明。

图: 变量声明。

DEFINE s SET( INTEGER NOT NULL );

变量 s 保存整数的 SET。每一 SET 是存储在 twin_primes 中的 LIST 的一个元素。

将集合选择至集合变量内​

在您声明集合变量之后，您可将集合访存至它内。要将集合访存至集合变量内，请输入 SELECT INTO 语句，该语句从数据库将集合列选择至您已命名了的集合变量内。

例如，要选择保存在 numbers 的 primes 列的一行中的集合，请添加 SELECT 语句至您的 SPL 例程，诸如下图展示的一个。

图: 添加 SELECT 语句来选择存储在一行中的集合。

SELECT primes INTO p_coll FROM numbers
WHERE id = 220;

SELECT 语句中的 WHERE 子句指定您想要选择只存储在 numbers 的一行中的集合。该语句将集合放置到集合变量 p_coll 内，图 1 声明它。

现在，变量 p_coll 保存来自 primes 列的集合，它会包含值 SET {5,7,31,19,13}。

将元素插入至集合变量内​

在您将集合检索至集合变量内之后，您可将值插入至该集合变量。INSERT 语句的语法略有不同，这依赖于您想要条件到的 集合的类型。

插入至 SET 或 MULTISET 内​

要插入至存储在集合变量中的 SET 或 MULTISET 内，请使用 INSERT 语句，并跟在带有集合变量的 TABLE 关键字之后，如下图所示。

图: 插入至存储在集合变量中的 SET 或 MULTISET 内。

INSERT INTO TABLE(p_coll) VALUES(3);

TABLE 关键字使得集合变量成为集合派生的表。在 处理 SELECT 语句中的集合 部分中描述集合派生的表。前图派生的集合是一列的虚拟表，集合的每一元素表示表的一行。在插入之前，请将 p_coll 考虑作为包含下图展示的行（元素）的虚拟表。

图: 虚拟表元素。

5
7
31
19
13

在插入之后，p_coll 可能看上去像下图所示的虚拟表一样。

图: 虚拟表元素。

5
7
31
19
13
3

由于该集合为 SET，因此，将新的值添加到该集合，但未定义新元素的位置。对于 MULTISET ，适用同样的原理。

提示

您一次仅可将一个值插入至简单的集合内。

插入至 LIST 内​

如果集合为 LIST，则您可将新的元素添加在 LIST 中的特定点，或添加在 LIST 的末尾。如同 SET 或 MULTISET 一样，您必须首先定义集合变量，并从数据库将集合选择至该集合变量内。

下图展示您需要定义集合变量并从 numbers 表选择 LIST 至该集合变量内的语句。

图: 定义集合变量并选择 LIST。

DEFINE e_coll LIST(INTEGER NOT NULL);
SELECT evens INTO e_coll FROM numbers
WHERE id = 99;

此时，e_coll 的值可能为 LIST {2,4,6,8,10}。由于 e_coll 保存 LIST，因此，每一元素有在该列表中的编号的位置。要将元素添加在 LIST 中特定的位置，请将 AT position 子句添加到 INSERT 语句，如下图所示。

图: 将元素添加在 LIST 中特定的点。

INSERT AT 3 INTO TABLE(e_coll) VALUES(12);

现在，e_coll 中的 LIST 有元素 {2,4,12,6,8,10}，依此顺序。

您在 AT 子句中为 position 输入的值可为数值或变量，但它必须有 INTEGER 或 SMALLINT 数据类型。您不可使用字母、浮点数值、十进制值或表达式。

检查 LIST 集合的基数​

有时，您可能想要将元素添加在 LIST 的末尾。在此情况下，您可使用 cardinality() 函数来找到 LIST 中的元素的编号，然后输入一个大于cardinality() 返回的值的位置。

GBase 8s 允许您随同存储在列中的集合来使用 cardinality() 函数，但不允许随同存储在集合变量中的集合来使用。在 SPL 例程中，您可以 SELECT 语句检查列中的集合的基数，并将该值返回给变量。

假设在 numbers 表中，其 id 列为 99 的那一行的 evens 列仍然包含集合 LIST {2,4,6,8,10}。这一次，您想要将元素 12 添加在该 LIST 的末尾。您可使用 SPL 过程 end_of_list 实现，如下图所示。

图: end_of_list SPL 过程。

CREATE PROCEDURE end_of_list()

DEFINE n SMALLINT;
DEFINE list_var LIST(INTEGER NOT NULL);

SELECT CARDINALITY(evens) FROM numbers INTO n
WHERE id = 100;

LET n = n + 1;

SELECT evens INTO list_var FROM numbers
WHERE id = 100;

INSERT AT n INTO TABLE(list_var) VALUES(12);

END PROCEDURE;

在 end_of_list 中，变量 n 保存 cardinality() 返回的值，即，LIST 中的项数。LET 语句使 n 递增，以便于 INSERT 语句可在 LIST 的最后的位置插入值。SELECT 语句将来自该表的一行的集合选择至集合变量 list_var 内。INSERT 语句将元素 12 插入在该列表的结尾。

VALUES 子句的语法​

当您插入至 SPL 集合变量内时，VALUES 子句的语法与当您插入至集合列内时是不一样的。将文字插入至集合变量内的语法规则如下：

在 VALUES 关键字之后使用圆括号来括起值的完整列表。

如果您正在插入至简单的集合内，则无需使用类型构造函数或方括号。

如果您正在插入至嵌套的集合，则您需要指定文字的集合。

从集合选择元素​

假设您想要您的 SPL 例程从存储在集合变量内的集合选择元素，则请一次选择一个，以便于您可处理这些元素。

要在集合的元素间移动，您首先需要使用 FOREACH 语句来声明游标，就如同您会声明游标来在一组行间一同一样。下图展示 FOREACH 和 END FOREACH 语句，在它们之间还没有语句。

图: FOREACH 和 END FOREACH 语句。

FOREACH cursor1 FOR
. . .
END FOREACH

在 FOREACH 循环 和 GBase 8s SQL 指南：语法 中描述 FOREACH 语句。

下一主题，集合查询，描述在 FOREACH 与 END FOREACH 语句之间省略的那些语句。

下列部分中的示例是基于 图 2 的 polygons 表的。

集合查询​

在您在 FOREACH 与 END FOREACH 语句之间声明游标之后，请您输入称为集合查询的特殊的、受限形式的 SELECT 语句。

集合查询是使用后跟集合变量的名称的 FROM TABLE 关键字的 SELECT 语句。下图展示此结构，称其为集合派生的表。

图: 集合派生的表。

FOREACH cursor1 FOR
SELECT * INTO pnt FROM TABLE(vertexes)
. . .
END FOREACH

该 SELECT 语句使用集合变量 vertexes 作为集合派生的表。您可将集合派生的表视为一列的表，该集合的每一元素都是表的一行。例如，您可将存储在 vertexes 中的四个点的 SET 可视化作为带有四行的表，诸如下图展示的一个。

图: 带有四行的表。

'(3.0,1.0)'
'(8.0,1.0)'
'(3.0,4.0)'
'(8.0,4.0)'

在前图中的 FOREACH 语句的第一个迭代之后，该集合查询选择 vertexes 中的第一个元素，并将其存储在 pnt 中，因此，pnt 包含值 '(3.0,1.0)'。

提示

由于集合变量 vertexes 包含 SET，而不是 LIST，因此，vertexes 中的元素没有定义了的顺序。在真实的数据库中，值 '(3.0,1.0)' 可能不是 SET 中的第一个元素。

将集合查询添加至 SPL 例程​

现在，您可将以 FOREACH 定义的游标和集合查询添加至 SPL 例程，如下例所示。

图: 以 FOREACH 定义的游标和集合查询。

CREATE PROCEDURE shapes()

DEFINE vertexes SET( point NOT NULL );
DEFINE pnt point;

SELECT definition INTO vertexes FROM polygons
WHERE id = 207;

FOREACH cursor1 FOR
SELECT * INTO pnt FROM TABLE(vertexes)
. . .
END FOREACH
. . .
END PROCEDURE;

以上展示的语句形成处理集合变量的元素的 SPL 例程的框架。要将集合分解为它的元素，请使用集合派生的表。在将集合分解为它的元素之后，该例程可单独地访问元素作为集合派生的表的行。既然您已选择了 pnt 中的一个元素，您就可更新或删除那个元素，如 更新集合元素 和 删除集合元素 描述的那样。

要了解集合查询的完整语法，请参阅《GBase 8s SQL 指南：语法》 中的 SELECT 语句。要了解集合派生的表的语法，请参阅《GBase 8s SQL 指南：语法》 中的“集合派生的表”段。

提示

如果您正在从不包含元素或包含零元素的集合选择，则您可使用未声明游标的集合查询。然而，如果该集合包含多个元素，且您未使用游标，则您会收到错误消息。

注意

在上述程序段中，如果 FOREACH 游标定义内的查询（

SELECT * INTO pnt FROM TABLE(vertexes)

）已以分号（;）作为语句终止符终止了，则数据库服务器可能已发出了语法错误。在此，END FOREACH 关键字是逻辑的语句终止符。

删除集合元素​

在您将个别的元素从集合变量选择至元素变量内之后，您可从集合删除该元素。例如，在您以集合查询从集合变量 vertexes 选择一个点之后，您可将该点从集合移除。

删除集合元素涉及的步骤包括：

  1. 声明集合变量和元素变量。
  2. 将集合从数据库选择至集合变量内。
  3. 声明游标，以便于您可从集合变量一次选择一个元素。
  4. 编写定位您想要删除的元素的循环或分支。
  5. 使用 DELETE WHERE CURRENT OF 语句来从集合删除元素，该语句使用集合变量作为集合派生的表。

下图展示删除 vertexes 中四个点之一的例程，以便于多边形成为三角形，而不是矩形。

图: 删除四个点之一的例程。

CREATE PROCEDURE shapes()

DEFINE vertexes SET( point NOT NULL );
DEFINE pnt point;

SELECT definition INTO vertexes FROM polygons
WHERE id = 207;

FOREACH cursor1 FOR
SELECT * INTO pnt FROM TABLE(vertexes)
IF pnt = '(3,4)' THEN
-- calls the equals function that
-- compares two values of point type
DELETE FROM TABLE(vertexes)
WHERE CURRENT OF cursor1;
EXIT FOREACH;
ELSE
CONTINUE FOREACH;
END IF;
END FOREACH
. . .
END PROCEDURE;

在前图中，FOREACH 语句声明游标。SELECT 语句是集合派生的查询，从集合变量 vertexes 一次将选择一个元素至元素变量 pnt。

IF THEN ELSE 结构测试当前在 pnt 中的值，看它是否为点 '(3,4)'。请注意，表达式 pnt = '(3,4)' 调用在点数据类型上定义的 equal() 函数的实例。如果 pnt 中的当前值为 '(3,4)'，则 DELETE 语句删除它，且 EXIT FOREACH 语句退出该游标。

提示

从存储在集合变量中的集合删除元素，未将它从存储在数据库中的集合删除。在您从集合变量删除元素之后，您必须以新的集合更新存储在数据库中的集合。要了解展示如何更新集合列的示例，请参阅 更新数据库中的集合。

在《GBase 8s SQL 指南：语法》中描述 DELETE 语句的语法。

更新数据库中的集合​

在您（通过删除、更新或插入元素）更改 SPL 例程中集合变量的内容之后，您必须以新的集合更新数据库。

要更新数据库中的集合，请添加一个设置表中的集合列的 UPDATE 语句，设置为更新了的集合变量的内容。例如，下图中的 UPDATE 语句展示如何更新 polygons 表，来将 definition 列设置为存储在集合变量 vertexes 中的新集合。

图: 更新数据库中的集合。

CREATE PROCEDURE shapes()

DEFINE vertexes SET(point NOT NULL);
DEFINE pnt point;

SELECT definition INTO vertexes FROM polygons
WHERE id = 207;

FOREACH cursor1 FOR
SELECT * INTO pnt FROM TABLE(vertexes)
IF pnt = '(3,4)' THEN
-- calls the equals function that
-- compares two values of point type
DELETE FROM TABLE(vertexes)
WHERE CURRENT OF cursor1;
EXIT FOREACH;
ELSE
CONTINUE FOREACH;
END IF;
END FOREACH

UPDATE polygons SET definition = vertexes
WHERE id = 207;

END PROCEDURE;

现在，shapes() 例程完成。在您运行 shapes() 之后，更新存储在其 ID 列为 207 的行中的集合，以便于它包含三个值，而不是四个。

您可使用 shapes() 例程作为框架，来编写操纵集合的其他 SPL 例程。

出现在存储在 polygons 表的 207 的 definition 列中的集合的元素罗列如下：

'(3,1)'
'(8,1)'
'(8,4)'

删除整个集合​

如果您想要删除集合的所有元素，您可使用单个 SQL 语句。您无需声明游标。要删除整个集合，您必须执行下列任务：

  • 定义集合变量。
  • 将该集合从数据库选择至集合变量内。
  • 输入使用集合变量作为集合派生的表的 DELETE 语句。
  • 更新来自数据库的集合。

下图展示您可能在 SPL 例程中用于删除整个集合的语句。

图: 删除整个集合的 SPL 例程。

DEFINE vertexes SET( INTEGER NOT NULL );

SELECT definition INTO vertexes FROM polygons
WHERE id = 207;

DELETE FROM TABLE(vertexes);

UPDATE polygons SET definition = vertexes
WHERE id = 207;

此种形式的 DELETE 语句删除集合变量 vertexes 中的整个集合。您不可使用一个使用集合派生的表的 DELETE 语句中的 WHERE 子句。

在 UPDATE 语句之后，polygons 表包含空的集合，其中 id 列等于 207。

在 GBase 8s SQL 指南：语法 中描述 DELETE 语句的语法。

更新集合元素​

您可通过访问游标内的集合来更新集合元素，就如同您选择或删除个别的元素一样。

如果您想要更新集合 SET{100, 200, 300, 500} 来将值 500 更改为 400，请从数据库将该 SET 检索至集合变量内，然后声明游标来在 SET 中的元素间移动，如下图所示。

图: 更新集合元素。

DEFINE s SET(INTEGER NOT NULL);
DEFINE n INTEGER;

SELECT numbers INTO s FROM orders
WHERE order_num = 10;

FOREACH cursor1 FOR
SELECT * INTO n FROM TABLE(s)
IF ( n == 500 ) THEN
UPDATE TABLE(s)(x)
SET x = 400 WHERE CURRENT OF cursor1;
EXIT FOREACH;
ELSE
CONTINUE FOREACH;
END IF;
END FOREACH

UPDATE 语句使用集合变量 s 作为集合派生的表。要指定集合派生的表，请使用 TABLE 关键字。在 UPDATE 语句中跟在 (s) 之后的值 (x) 是您提供的列名称 derived column，因为 SET 子句需要它，即使集合派生的表没有列也需要。

请将集合派生的表视作有一行，且看起来与下列示例有些相似：

100 200 300 500

在此示例中，x 是包含值 500 的“列”的虚构的列名称。如果您正在更新内建的、opaque、distinct 或集合类型元素的集合，则仅指定派生的列。如果您正在更新 row 类型的集合，请使用字段名，而不是派生的列，如 更新 row 类型的集合 描述的那样。

使用变量更新集合​

您还可使用存储在变量中的值，而不是文字值，来更新集合。

下图中的 SPL 过程使用类似于 图 1 展示的语句，除了此过程是使用变量，而不是文字值，来更新 manager 表的 direct_reports 列中的 SET。图 2 定义该 manager 表。

图: 使用变量更新集合。

CREATE PROCEDURE new_report(mgr VARCHAR(30),
old VARCHAR(30), new VARCHAR(30) )

DEFINE s SET (VARCHAR(30) NOT NULL);
DEFINE n VARCHAR(30);

SELECT direct_reports INTO s FROM manager
WHERE mgr_name = mgr;

FOREACH cursor1 FOR
SELECT * INTO n FROM TABLE(s)
IF ( n == old ) THEN
UPDATE TABLE(s)(x)
SET x = new WHERE CURRENT OF cursor1;
EXIT FOREACH;
ELSE
CONTINUE FOREACH;
END IF;
END FOREACH

UPDATE manager SET mgr_name = s
WHERE mgr_name = mgr;

END PROCEDURE;

嵌套在 FOREACH 循环中的 UPDATE 语句使用集合派生的表 s 和派生的列 x。如果 n 的当前值与 old 相同，则 UPDATE 语句将它更改为 new 的值。第二个 UPDATE 语句在 manager 表中存储新集合。

更新整个集合​

如果您想要将集合中的所有元素更新为相同的值，或如果该集合仅包含一个值，则您无需使用游标。下图中的语句展示你可如何将集合检索至集合变量内，然后使用一个语句来更新它。

图: 检索并更新集合。

DEFINE s SET (INTEGER NOT NULL);

SELECT numbers INTO s FROM orders
WHERE order_num = 10;

UPDATE TABLE(s)(x) SET x = 0;

UPDATE orders SET numbers = s
WHERE order_num = 10;

此示例中的第一个 UPDATE 语句随同集合派生的表 s 使用名为 x 的派生的列，并将值 0 赋给集合中的所有元素。第二个 UPDATE 语句在数据库中存储新的集合。

更新 row 类型的集合​

要更新 ROW 类型的集合，您可采用这些步骤：

  1. 声明集合变量，其字段数据类型与该集合中的那些 ROW 类型相匹配。
  2. 将集合变量的个别字段设置为 ROW 类型的正确数据值。
  3. 对于每一 ROW 类型，请使用集合变量更新集合派生的表的全部行。

图 2 中的 manager 表有一名为 projects 的列，包含在下图展示其定义的 ROW 类型的 LIST。

图: ROW 类型的 LIST 定义。 
projects LIST( ROW( pro_name VARCHAR(15),
pro_members SET(VARCHAR(20) NOT NULL) ) NOT NULL)

要访问 LIST 中的 ROW 类型，请声明游标，并将该 LIST 选择至集合变量内。然而，在您检索 projects 列中每一 ROW 类型值之后，您不可个别地更新 pro_name 或 pro_members 字段。相反，对于需要在集合中更新的每一 ROW 值，您必须将整个 ROW 替换为来自包括新字段值的集合变量的值，如下图所示。

图: 访问 LIST 中的 ROW 类型。

CREATE PROCEDURE update_pro( mgr VARCHAR(30),
pro VARCHAR(15) )

DEFINE p LIST(ROW(a VARCHAR(15), b SET(VARCHAR(20)
NOT NULL) ) NOT NULL);
DEFINE r ROW(p_name VARCHAR(15), p_member SET(VARCHAR(20) NOT NULL) );
LET r = ROW("project", "SET{'member'}");

SELECT projects INTO p FROM manager
WHERE mgr_name = mgr;

FOREACH cursor1 FOR
SELECT * INTO r FROM TABLE(p)
IF (r.p_name == 'Zephyr') THEN
LET r.p_name = pro;
UPDATE TABLE(p)(x) SET x = r
WHERE CURRENT OF cursor1;
EXIT FOREACH;
END IF;
END FOREACH

UPDATE manager SET projects = p
WHERE mgr_name = mgr;

END PROCEDURE;

在您可在 SPL 程序中使用 row 类型变量之前，您必须使用 LSET 语句或 SELECT INTO 语句来初始化该行变量。前图的 FOREACH 循环中嵌套的 UPDATE 语句将 row 类型的 pro_name 字段设置为变量 pro 中提供的值。

提示

要更新 ROW 类型的 pro_members 字段中 SET 中的值，请声明游标，并使用带有派生的列的 UPDATE 语句，如 更新集合元素 说明的那样。

更新嵌套的集合​

如果您想要更新集合的集合，则必须声明游标来访问外层的集合，然后声明嵌套的游标来访问内层的集合。

例如，假设 manager 表有一附加的列 scores，它包含一其元素类型为整数的 MULTISET 的 LIST，如下图所示。

图: 更新集合的集合。

scores LIST(MULTISET(INT NOT NULL) NOT NULL);

要更新 MULTISET 中的值，请声明在 LIST 中每一值间移动的游标，以及在 MULTISET 中 每一值间移动的嵌套的游标，如下图所示。

图: 更新 MULTISET 中的值。

CREATE FUNCTION check_scores ( mgr VARCHAR(30) )
SPECIFIC NAME nested;
RETURNING INT;

DEFINE l LIST( MULTISET( INT NOT NULL ) NOT NULL );
DEFINE m MULTISET( INT NOT NULL );
DEFINE n INT;
DEFINE c INT;

SELECT scores INTO l FROM manager
WHERE mgr_name = mgr;

FOREACH list_cursor FOR
SELECT * FROM TABLE(l) INTO m;

FOREACH set_cursor FOR
SELECT * FROM TABLE(m) INTO n;
IF (n == 0) THEN
DELETE FROM TABLE(m)
WHERE CURRENT OF set_cursor;
ENDIF;
END FOREACH;
LET c = CARDINALITY(m);
RETURN c WITH RESUME;
END FOREACH

END FUNCTION
WITH LISTING IN '/tmp/nested.out';

该 SPL 函数将 scores 列中的每一 MULTISET 选择至 l 内，然后将 MULTISET 中的每一值选择至 m 内。如果 m 中的值为 0，则函数从 MULTISET 删除它。在删除 0 的值之后，该函数统计每一 MULTISET 中剩余的元素数，并返回一整数。

提示

由于此函数为 LIST 中每一 MULTISET 返回一值，因此，当您执行该函数时，您必须使用游标来括起 EXECUTE FUNCTION 语句。

插入至集合内​

您可将值插入至集合内，而不声明游标。如果该集合为 SET 或 MULTISET，则将该值添加到集合，但不定义新元素的位置，因为该集合没有特定的顺序。如果该值为 LIST，则您可将新元素添加在 LIST 中特定的位置，或添加在 LIST 的末尾。

在 manager 表中，direct_reports 列包含 SET 类型的集合，且 projects 列包含 LIST。要将名称添加到 direct_reports 列中的 SET，请使用带有集合派生的表的 INSERT 语句，如下图所示。

图: 将值插入至集合。

CREATE PROCEDURE new_emp( emp VARCHAR(30), mgr VARCHAR(30) )

DEFINE r SET(VARCHAR(30) NOT NULL);

SELECT direct_reports INTO r FROM manager
WHERE mgr_name = mgr;

INSERT INTO TABLE (r) VALUES(emp);

UPDATE manager SET direct_reports = r
WHERE mgr_name = mgr;

END PROCEDURE;

此 SPL 过程将员工姓名和管理者姓名作为参数。然后，该过程为用户已输入了的管理者选择 direct_reports 列中的集合，添加用户已输入了的员工名称，并以新集合更新 manager 表。

前图中的 INSERT 语句将用户提供的新的员工姓名插入至在集合变量 r 中包含的 SET 内。然后，UPDATE 语句将新集合存储在 manager 表中。

请注意 VALUES 子句的语法。将文字的数据和变量插入至集合变量内的语法规则如下：

  • 使用 VALUES 关键字之后的圆括号来括起值的完整列表。

  • 如果该集合为 SET、MULTISET 或 LIST，则请使用后跟方括号的类型构造函数来将要插入的值的列表括起来。此外，必须将集合值括在引号中。

    VALUES( "SET{ 1,4,8,9 }" )

  • 如果该集合包含 row 类型，请使用后跟圆括号的 ROW 来将要插入的值的列表括起来：

    VALUES( ROW( 'Waters', 'voyager_project' ) )

  • 如果该集合为嵌套的集合，则根据定义数据类型的方式，嵌套关键字、圆括号和方括号：

    VALUES( "SET{ ROW('Waters', 'voyager_project'),
    ROW('Adams', 'horizon_project') }")

要获取关于将值插入至集合的信息，请参阅 修改数据。

插入至嵌套的集合内​

如果您想要插入至嵌套的集合内，则 VALUES 子句的语法有变化。例如，假设您想要将值插入至 图 1 展示的 numbers 表的 twin_primes 列内。

对于 twin_primes 列，您可能想要将 SET 插入至 LIST，或将元素插入至内层的 SET。下列部分描述每一任务。

将集合插入至外层的集合内​

将 SET 插入至 LIST 内，类似于将单个值插入至简单的集合内。

要将 SET 插入至 LIST 内，请声明集合变量来保存该 LIST，并将整个集合选择至它内。当您使用集合变量作为集合派生的表时，该 LIST 中的每一 SET 成为该表的一行。然后，您可将另一 SET 插入在该 LIST 的末尾或插入在指定的点。

例如，一个数值行的 twin_primes 列可能包含下列 LIST，如下图所示。

图: 样例 LIST。

LIST( SET{3,5}, SET{5,7}, SET{11,13} )

如果您将 LIST 视为集合派生的表，则它看上去可能像这样。

图: 将 LIST 视为集合派生的表。

{3,5}
{5,7}
{11,13}

您可能想要插入值 "SET{17,19}" 作为 LIST 中的第二项。下图中的语句展示如何执行。

图: 将值插入到 LIST 中。

CREATE PROCEDURE add_set()

DEFINE l_var LIST( SET( INTEGER NOT NULL ) NOT NULL );

SELECT twin_primes INTO l_var FROM numbers
WHERE id = 100;

INSERT AT 2 INTO TABLE (l_var) VALUES( "SET{17,19}" );

UPDATE numbers SET twin_primes = l
WHERE id = 100;

END PROCEDURE;

在 INSERT 语句中，VALUES 子句将值 SET {17,19} 插入在 LIST 的第二个位置。现在，该 LIST 看上去像下图这样。

图: LIST 项。

{3,5}
{17,19}
{5,7}
{11,13}

通过将 SET 作为参数传到 SPL 例程，您可执行相同的插入，如下图所示。

图: 将 SET 作为参数传到 SPL 例程。

CREATE PROCEDURE add_set( set_var SET(INTEGER NOT NULL),
row_id INTEGER );

DEFINE list_var LIST( SET(INTEGER NOT NULL) NOT NULL );
DEFINE n SMALLINT;

SELECT CARDINALITY(twin_primes) INTO n FROM numbers
WHERE id = row_id;

LET n = n + 1;

SELECT twin_primes INTO list_var FROM numbers
WHERE id = row_id;

INSERT AT n INTO TABLE( list_var ) VALUES( set_var );

UPDATE numbers SET twin_primes = list_var
WHERE id = row_id;

END PROCEDURE;

在 add_set() 中，用户提供 SET 来添加到 LIST，以及标识将 SET 插入其中的那行的 id 的 INTEGER 值。

将值插入至内层的集合​

在 SPL 例程中，您还可将值插入至嵌套的集合的内层集合。通常，要访问嵌套的集合的内层集合并将值插入到它，请执行下列步骤：

  1. 声明集合变量来在表的一行中保存整个集合。
  2. 声明元素变量来保存该外层的集合的一个元素。元素变量本身是集合变量。
  3. 将整个集合从表的一行选择至集合变量。
  4. 声明游标，以便您可在外层的集合的元素间移动。
  5. 一次将一个元素选择至元素变量内。
  6. 请使用分支或循环来定位您想要更新的内层集合。
  7. 将新值插入至内层的集合内。
  8. 关闭游标。
  9. 以新的集合更新数据库表。

作为示例，您可在 numbers 的 twin_primes 列上使用此过程。 例如，假设 twin_primes 包含下图所示的值，且您想要将值 18 插入至 LIST 的最后的 SET 中。

图: twin_primes 列表。

LIST( SET( {3,5}, {5,7}, {11,13}, {17,19} ) )

下图展示插入该值的过程的开始。

图: 插入值的过程。

CREATE PROCEDURE add_int()

DEFINE list_var LIST( SET( INTEGER NOT NULL ) NOT NULL );
DEFINE set_var SET( INTEGER NOT NULL );

SELECT twin_primes INTO list_var FROM numbers
WHERE id = 100;

至此，attaint 过程已执行了步骤 1、2 和 3。第一个 DEFINE 语句声明保存在一个数值行中的整个集合的集合变量。

第二个 DEFINE 语句声明保存该集合的元素的元素变量。在此情况下，元素变量本身是集合变量，因为它保存 SET。SELECT 语句将整个集合从一行选择至集合变量 list_var 内。

下图展示如何声明游标，以便于您可在外层的集合的元素间移动。

图: 声明游标来在外层的集合的元素间移动。

FOREACH list_cursor FOR
SELECT * INTO set_var FROM TABLE( list_var);

FOREACH element_cursor FOR

执行例程​

您可以下列任一方式执行 SPL 例程或外部例程：

  • 使用从 DB-Access 执行的单独的 EXECUTE PROCEDURE 或 EXECUTE FUNCTION 语句
  • 从另一 SPL 例程或外部例程显式地调用例程
  • 在 SQL 语句中使用带有表达式的例程名称

执行例程的附加的机制仅支持 sysdbopen 和 sysdbclose 过程，DBA 可定义这些过程。当用户通过 CONNECT 或 DATABASE 语句连接到数据库时，如果 sysdbopen 过程的所有者与数据库中存在的用户的登录标识符相匹配，则自动地执行那个例程。如果没有 sysdbopen 例程的所有者与该用户的登录标识符相匹配，但存在 PUBLIC.sysdbopen 例程，则执行那个例程。这种自动的调用使得 DBA 能够在连接时刻为用户定制会话环境。当用户从数据库断开连接时，类似地调用 sysdbclose 例程。（要获取关于这些会话配置例程的更多信息，请参阅《GBase 8s SQL 指南：语法》和 GBase 8s 管理员指南。）

外部例程是以 C 或某种其他外部语言编写的例程。

EXECUTE 语句​

您可使用 EXECUTE PROCEDURE 或 EXECUTE FUNCTION 来执行 SPL 例程或外部例程。通常，最好将 EXECUTE PROCEDURE 用于过程，将 EXECUTE FUNCTION 用于函数。

提示

为了向后兼容， EXECUTE PROCEDURE 语句允许您使用 SPL 函数名称和 INTO 子句来返回值。然而，推荐您仅将 EXECUTE PROCEDURE 用于过程，仅将 EXECUTE FUNCTION 用于函数。

您可从 DB-Access 或从 SPL 例程或外部例程内，发出 EXECUTE PROCEDURE 和 EXECUTE FUNCTION 语句作为独立的语句。如果在数据库内该例程名称是唯一的，且如果它不需要参数，则您可通过在 EXECUTE PROCEDURE 之后只输入它的名称和圆括号来执行它，如下图所示。

图: 执行过程。

EXECUTE PROCEDURE update_orders();

由于过程不返回任何值，因此，当您以 EXECUTE 语句调用过程时，从不出现 INTO 子句。

如果例程期望参数，则您必须在圆括号内输入参数值，如下图所示。

图: 执行带有参数的过程。

EXECUTE FUNCTION scale_rectangles(107, 1.9)
INTO new;

该语句执行函数。由于函数返回值，因此，EXECUTE FUNCTION 使用 INTO 子句，指定存储返回值的变量。当您使用 EXECUTE 语句来执行函数时，始终出现 INTO 子句。

如果数据库有多个同名的过程或函数，则 GBase 8s 基于参数的数据类型来定位到正确的函数。例如，前图中的语句提供 INTEGER 和 REAL 值作为参数，因此，如果您的数据库包含名为 scale_rectangles() 的多个例程，在数据库服务器仅执行接受 INTEGER 和 REAL 数据类型的scale_rectangles() 函数。

SPL 例程的参数列表始终有参数名称及数据类型。当您执行例程时，参数名称是可选的。然而，如果您通过名称（而不是只通过值）来将参数传到 EXECUTE PROCEDURE 或 EXECUTE FUNCTION，如下图所示，则 GBase 8s 仅逐个例程地解析名称和参数，该过程称为部分的例程解析。

图: 执行通过名称传递参数的例程。

EXECUTE FUNCTION scale_rectangles( rectid = 107,
scale = 1.9 ) INTO new_rectangle;

您还可通过将限定的例程名称添加到语句来执行存储在另一数据库服务器上的 SPL 例程；即，database@dbserver:owner_name.routine_name 形式的名称，如下图所示。

图: 执行存储在另一数据库服务器上的 SPL 例程。

EXECUTE PROCEDURE gbasedbt@davinci:bsmith.update_orders();

当您远程地执行例程时，限定的例程名称中的 owner_name 是可选的。

CALL 语句​

您可使用 CALL 语句，从 SPL 例程调用 SPL 例程或外部例程。CALL 可执行过程，也可执行函数。如果您使用 CALL 来执行函数，则请添加 RETURNING 子句和将要接收该函数返回的值的 SPL 变量的名称。

例如，假设您想要 scale_rectangles 函数调用计算矩形面积的外部函数，然后返回带有矩形描述的面积，如下图所示。

图: 调用外部函数。

CREATE FUNCTION scale_rectangles( rectid INTEGER,
scale REAL )
RETURNING rectangle_t, REAL;

DEFINE rectv rectangle_t;
DEFINE a REAL;
SELECT rect INTO rectv
FROM rectangles WHERE id = rectid;
IF ( rectv IS NULL ) THEN
LET rectv.start = (0.0,0.0);
LET rectv.length = 1.0;
LET rectv.width = 1.0;
LET a = 1.0;
RETURN rectv, a;
ELSE
LET rectv.length = scale * rectv.length;
LET rectv.width = scale * rectv.width;
CALL area(rectv.length, rectv.width) RETURNING a;
RETURN rectv, a;
END IF;

END FUNCTION;

该 SPL 函数使用执行外部函数 area() 的 CALL 语句。返回的值 area() 保存在 a 中，并通过 RETURN 语句返回到调用例程。

在此示例中，area() 是外部函数，但您可以同样的方式将 CALL 用于 SPL 函数。

执行表达式中的例程​

正如内建的函数那样，您可通过在 SQL 和 SPL 语句中的表达式，使用 SPL 例程来执行 SPL 例程（以及来自 SPL 例程的外部例程）。表达式中使用的例程通常为函数，因为它将值返回至语句的剩余部分。

例如，您可能通过将返回值分配给变量的 LET 语句来执行函数。下图中的语句执行相同的任务。它们执行 SPL 例程内的外部函数，并将返回值分配给变量 a。

图: 执行 SPL 例程内的外部函数。

LET a = area( rectv.length, rectv.width );

CALL area( rectv.length, rectv.width ) RETURNING a;
-- 这些语句是等同的

您还可从 SQL 语句执行 SPL 例程，如下图所示。假设您编写 SPL 函数 increase_by_pct，对给定的价格增加给定的百分比。在您编写 SPL 例程之后，在任何其他 SPL 例程中都可使用它。

图: 从 SQL 语句执行 SPL 例程。

CREATE FUNCTION raise_price ( num INT )
RETURNING DECIMAL;

DEFINE p DECIMAL;

SELECT increase_by_pct(price, 20) INTO p
FROM inventory WHERE prod_num = num;

RETURN p;

END FUNCTION;

该示例选择 inventory 的指定的行的 price 列，并使用该值作为 SPL 函数 increase_by_pct 的参数。然后，该函数返回新的 price 值，在变量中增加 20%。

使用 RETURN 语句执行外部函数​

您可使用 RETURN 语句来从 SPL 例程内执行任何外部函数。下图展示在 SPL 程序的 RETURN 语句中使用的外部函数。

图: 从 SPL 例程内执行外部函数的 RETURN 语句。

CREATE FUNCTION c_func() RETURNS int
LANGUAGE C;

CREATE FUNCTION spl_func() RETURNS INT;
RETURN(c_func());
END FUNCTION;

EXECUTE FUNCTION spl_func();

当您执行 spl_func() 函数时，调用 c_func() 函数，且 SPL 函数返回外部函数返回的值。

从 SPL 例程执行游标函数​

游标函数是返回一行或多行数据的用户定义的函数，因此需要游标来执行。游标函数可为系列函数之一：

  • 其 RETURN 语句包括 WITH RESUME 的 SPL 函数
  • 定义作为迭代函数的外部函数

游标函数的行为与 SPL 函数或外部函数都一样。然而，SPL 游标函数每迭代可返回多个值，而外部游标函数（迭代函数）每迭代仅可返回一个值。

要从 SPL 例程执行游标函数，您必须在 SPL 例程的 FOREACH 循环中包括该函数。下列示例展示在 FOREACH 循环中执行游标函数的不同方式：

FOREACH SELECT cur_func1(col_name) INTO spl_var FROM tab1
INSERT INTO tab2 VALUES (spl_var);
END FOREACH

FOREACH EXECUTE FUNCTION cur_func2() INTO spl_var
INSERT INTO tab2 VALUES (spl_var);
END FOREACH

动态的例程名称规范​

通过在调用例程内构建被调用的例程的名称，动态的例程名称规范允许您从另一 SPL 例程执行 SPL 例程。动态的例程名称规范简化您编写调用另一 SPL 例程的 SPL 例程的方式，直到运行时才能知道另一例程的名称。数据库服务器允许您在 EXECUTE PROCEDURE 或 EXECUTE FUNCTION 语句中指定 SPL 变量，而不是 SPL 例程的显式的名称。

在下图中，SPL 过程 company_proc 更新大型的公司销售表，然后，分配名为 salesperson_proc 的 SPL 变量来保存更新另一较小表的动态地创建的 SPL 过程的名称，这个较小的表包含个别销售人员的每月销售情况。

图: 动态的例程名称规范。

CREATE PROCEDURE company_proc ( no_of_items INT,
itm_quantity SMALLINT, sale_amount MONEY,
customer VARCHAR(50), sales_person VARCHAR(30) )

DEFINE salesperson_proc VARCHAR(60);

-- 更新公司表
INSERT INTO company_tbl VALUES (no_of_items, itm_quantity,
sale_amount, customer, sales_person);

-- 生成变量 salesperson_proc 的过程名称
LET salesperson_proc = sales_person || "." || "tbl" ||
current_month || "_" || current_year || "_proc" ;

-- 执行 salesperson_proc 变量指定的
-- SPL 过程
EXECUTE PROCEDURE salesperson_proc (no_of_items,
itm_quantity, sale_amount, customer)
END PROCEDURE;

在示例中，过程 company_proc 接受五个参数，并将它们插入至 company_tbl 内。然后，LET 语句使用不同的值和连接运算符 || 来产生要执行的另一 SPL 过程的名称。在 LET 语句中：

sales_person

传给 company_proc 过程的参数。

current_month

系统日期中的当前月份。

current_year

系统日期中的当前年份。

因此，如果名为 Bill 的销售人员在 1988 年 7 月完成一笔销售，则 company_proc 在 company_tbl 中插入一记录，并执行 SPL 过程bill.tbl07_1998_proc，更新包含个别销售人员的每月销售情况的较小的表。

动态例程名称规范的规则​

您必须定义保存动态地执行的 SPL 例程的名称的 SPL 变量为 CHAR、VARCHAR、NCHAR 或 NVARCHAR 类型。您还必须为 SPL 变量提供一个有效的且非 NULL 的名称。

在可执行动态的例程名称规范标识的 SPL 例程之前，该例程必须存在。如果你将有效的 SPL 例程的名称分配给该 SPL 变量，则 EXECUTE PROCEDURE 或 EXECUTE FUNCTION 语句执行在该变量中包含其名称的例程，即使存在同名的内建的函数。

在 EXECUTE PROCEDURE 或 EXECUTE FUNCTION 语句中，您不可使用两个 SPL 变量来创建形式为 owner.routine_name 的变量名称。然而，您可使用包含完全限定的例程名称的 SPL 变量，例如，bill.proc1。下图同时展示这两种情况。

图: 包含完全限定的例程名称的 SPL 变量。

EXECUTE PROCEDURE owner_variable.proc_variable;
-- 不允许这样

LET proc1 = bill.proc1;
EXECUTE PROCEDURE proc1; -- 允许这样

对例程的权限​

权限将可创建例程的用户与可执行例程的用户区分开来。有些权限表现为其他权限的一部分。例如，DBA 权限包括创建例程、执行例程的权限，以及将这些权限授予其他用户的权限。

注册例程的权限​

要在数据库中注册例程，被授权的用户将 SPL 命令包含在 CREATE FUNCTION 或 CREATE PROCEDURE 语句中。数据库服务器存储内部注册了的 SPL 例程。下列用户具有在数据库中注册新的例程的资格：

  • 有 DBA 权限的任何用户可在 CREATE 语句中，使用或不使用 DBA 关键字来注册例程。

    要了解 DBA 关键字的说明，请参阅 执行例程的 DBA 权限。

  • 没有 DBA 权限的用户需要 Resource 权限来注册 SPL 例程。该例程的创建者是所有者。

    没有 DBA 权限的用户不可使用 DBA 关键字来注册例程。

    DBA 必须给其他需要创建例程的用户授予 Resource 权限。DBA 还可撤销 Resource 权限，防止用户创建更多的例程。

  • 除了对在其中注册 UDR 的数据库保存持有 DBA 权限或 Resource 权限之外，创建 UDR 的用户还必须持有对以其编写 UDR 的编程语言的 Usage 权限。这些 SQL 可为特定的编程语言授予语言级别的 Usage 权限：

    • GRANT USAGE ON LANGUAGE C
    • GRANT USAGE ON LANGUAGE JAVA
    • GRANT USAGE ON LANGUAGE SPL

    除了个别的用户之外，这些权限的被授予者还可为用户定义的角色，或 PUBLIC 组。在将语言级别 Usage 权限授予角色之后，持有那个角色的任何用户都可通过使用 SQL 的 SET ROLE 语句使得该角色的所有访问权限能够指定那个角色作为当前的角色。

对于以 C 语言或 Java™ 语言编写的外部例程，如果启用 IFX_EXTEND_ROLE 配置参数，则仅 DBSA 已授予其 EXTERNAL 角色的用户可注册、删除或修改外部的 UDR 或 DataBlade 模块。在缺省情况下，启用此参数。通过将 IFX_EXTEND_ROLE 配置参数设置为 OFF 或设置为 0，DBSA 可禁用对持有 DataBlade 模块或外部 UDR 的 DDL 操作 EXTEND 角色的要求。然而，此安全特性对 SPL 例程不起作用。

总之，持有以上标识的数据库级别和语言级别自主访问控制权限（且还持有 EXTEND 角色，如果启用 IFX_EXTEND_ROLE 且该 UDR 为外部例程的话）的用户，可在下列 SQL 语句中引用 UDR：

  • DBA 或用户可以 CREATE FUNCTION、CREATE FUNCTION FROM、CREATE PROCEDURE、CREATE PROCEDURE FROM、CREATE ROUTINE 或 CREATE ROUTINE FROM 语句来注册新的 UDR。
  • DBA 或现有 UDR 的所有者可以 DROP FUNCTION、DROP PROCEDURE 或 DROP ROUTINE 语句来取消那个 UDR 的注册。
  • DBA 或现有 UDR 的所有者可以 ALTER FUNCTION、ALTER PROCEDURE 或 ALTER ROUTINE 语句来修改那个 UDR 的定义。

执行例程的权限​

Execute 权限使得用户能够调用例程。通过 EXECUTE 或 CALL 语句，或通过使用表达式中的函数可能调用例程。下列用户拥有缺省的 Execute 权限，这使得他们能够调用例程：

  • 在缺省情况下，任何具有 DBA 权限的用户都可执行数据库中的任何例程。
  • 如果以限定的 CREATE DBA FUNCTION 或 CREATE DBA PROCEDURE 语句注册该例程，则仅拥有 DBA 权限的用户对那个例程有缺省的 Execute 权限。
  • 如果数据库不符合 ANSI，则用户 public（任何拥有 Connect 数据库权限的用户）自动地拥有对例程的 Execute 权限，未以 DBA 关键字注册该例程。
  • 在符合 ANSI 的数据库中，过程所有者和任何拥有 DBA 权限的用户都可执行该例程，而无需收到附加的权限。

授予和撤销 Execute 权限​

例程有下列 GRANT 和 REVOKE 要求：

  • DBA 可将 Execute 权限授予数据库中的任何例程，也可撤销它。
  • 例程的创建者可授予或取消对那个特定的例程的 Execute 权限。通过包括带有 GRANT EXECUTE ON 语句的 AS grantor 子句，创建者丧失授予或撤销的能力。
  • 如果所有者在 GRANT EXECUTE ON 语句中应用了 WITH GRANT 关键字，则另一用户可授予 Execute 权限。

对于下列条件，DBA 或例程所有者必须显式地将 Execute 权限授予非 DBA 用户：

  • 以 DBA 关键字子句注册了的例程
  • 在符合 ANSI 的数据库中的例程
  • 不符合 ANSI 的数据库中的例程，但将 NODEFDAC 环境变量设置为 yes。

即使数据库服务器缺省地将权限授予 public，所有者也可限制对例程的 Execute 权限。为此，请发出 REVOKE EXECUTE ON PUBLIC 语句。DBA 和所有者仍可执行该例程，且如果使用的话，则可将 Execute 权限授予特定的用户。

使用 COMMUTATOR 和 NEGATOR 函数的 Execute 权限​

重要

如果您显式地授予对 SPL 函数的 Execute 权限，其为 UDR 的换向函数或否定函数，则在被授予者可使用任意函数之前，您还必须授予对换向函数或否定函数的那种权限。您不可随同 SPL 过程指定 COMMUTATOR 或 NEGATOR 修饰符。

下列示例演示对于函数的限制授权，以及将它的否定函数限定为一组用户。假设您创建下列否定函数对：

CREATE FUNCTION greater(y PERCENT, z PERCENT)
RETURNS BOOLEAN
NEGATOR= less(y PERCENT, z PERCENT);
. . .
CREATE FUNCTION less(y PERCENT, z PERCENT)
RETURNS BOOLEAN
NEGATOR= greater(y PERCENT, z PERCENT);

在缺省情况下，任何用户都可执行该函数和否定函数。下列函数仅允许 accounting 执行这些函数：

REVOKE EXECUTE ON FUNCTION greater FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION less FROM PUBLIC;
GRANT accounting TO mary, jim, ted;
GRANT EXECUTE ON FUNCTION greater TO accounting;
GRANT EXECUTE ON FUNCTION less TO accounting;

用户可能接收附带 WITH GRANT OPTION 授权的 Execute 权限来将 Execute 权限授予其他用户。如果用户失去对例程的 Execute 权限，则还从通过那个用户授予了 Execute 权限的那些用户撤销 Execute 权限。

要获取更多信息，请参阅《GBase 8s SQL 指南：语法》 中的 GRANT 和 REVOKE 语句描述。

对与例程相关联的对象的权限​

数据库服务器检查是否存在任何被引用的对象，并验证调用该例程的用户是否拥有访问被引用的对象的必要权限。

由例程引用的对象可包括：

  • 表和列
  • 序列对象
  • 用户定义的数据类型
  • 由该例程执行的其他例程

当例程运行时，定义有效的权限为下列的联合：

  • 运行该例程的用户的权限，
  • 带有 GRANT 选项的所有者的权限。

在缺省情况下，数据库管理员拥有数据库中带有 GRANT 选项的所有权限。因此，执行由数据库管理员拥有的例程的用户可从给定的数据库中所有表进行选择。

GRANT EXECUTE ON 语句提供给被授予者任何表级别权限，授予者从包含 WITH GRANT 关键字的 GRANT 语句收到这些权限。

该例程的所有者，不是运行该例程的用户，拥有在执行该例程过程中创建的未限定的对象。例如，假设用户 howie 注册创建两个表的 SPL 例程，使用下列 SPL 例程：

CREATE PROCEDURE promo()
. . .
CREATE TABLE newcatalog
(
catlog_num INTEGER
cat_advert VARCHAR(255, 65)
cat_picture BLOB
) ;
CREATE TABLE dawn.mailers
(
cust_num INTEGER
interested_in SET(catlog_num INTEGER)
);
END PROCEDURE;

用户 julia 运行该例程，创建表 newcatalog。由于没有所有者名称来限定表名称 newcatalog，因此，例程所有者（howie）拥有newcatalog。相比之下，限定的名称 dawn.maillist 标识 dawn 作为 maillist 的所有者。

执行例程的 DBA 权限​

如果 DBA 使用 DBA 关键字创建例程，则数据库服务器自动地仅将 Execute 权限授予有 DBA 权限的其他用户。然而，DBA 可显式地将 DBA 例程上的 Execute 权限授予没有 DBA 权限的用户。

当用户执行以 DBA 关键字注册了的例程时，该用户假设在例程持续期间持有 DBA 权限。如果没有 DBA 权限的用户运行 DBA 例程，则数据库服务器隐式地将临时的 DBA 权限授予调用者。在退出 DBA 例程之前，数据库服务器隐式地撤销该临时的 DBA 权限。

执行 DBA 例程的用户拥有在运行该 DBA 例程期间创建的对象， 除非例程中的语句显式地命名其他用户作为所有者。 例如，假设 tony 以 DBA 关键字注册 promo() 例程，如下：

CREATE DBA PROCEDURE promo()
. . .
CREATE TABLE catalog
. . .
CREATE TABLE libby.mailers
. . .
END PROCEDURE;

虽然 tony 拥有该例程，但如果 marty 运行它，那么 marty 拥有 catalog 表，但由于用户 libby 的名称限定 libby.mailers 表名称，使得她成为该表的所有者，因此它拥有该表。

被调用的例程未继承 DBA 权限。如果 DBA 例程执行未以 DBA 关键字创建了的例程，则 DBA 权限不影响被调用的例程。

如果未以 DBA 关键字注册的例程调用 DBA 例程，则调用者对于被调用的 DBA 例程必须有 Execute 权限。该 DBA 例程内的语句执行如同任何 DBA 例程内的语句一样。

下列示例展示当 DBA 与非 DBA 例程相互作用时发生的情况。假设过程 dbspc_cleanup() 执行另一过程 clust_catalog()。还假设 clust_catalog()创建索引，且 clust_catalog() 的 SPL 源代码包括下列语句：

CREATE CLUSTER INDEX c_clust_ix ON catalog (catalog_num);

DBA 过程 dbspc_cleanup() 以下列语句调用其他例程：

EXECUTE PROCEDURE clust_catalog(catalog);

假设 tony 注册了 dbspc_cleanup() 作为 DBA 过程，而未以 DBA 关键字注册 clust_catalog()，如下列语句所示：

CREATE DBA PROCEDURE dbspc_cleanup(loc CHAR)
CREATE PROCEDURE clust_catalog(catalog CHAR)
GRANT EXECUTE ON dbspc_cleanup(CHAR) to marty;

假设用户 marty 运行 dbspc_cleanup()。由于通过非 DBA 例程创建索引 c_clust_ix，因此，同时拥有两个例程的 tony 也拥有 c_clust_ix。相对地，如果 clust_catalog() 为 DBA 过程，则 marty 会拥有索引 c_clust_ix，如下列注册和授权语句所示：

CREATE PROCEDURE dbspc_cleanup(loc CHAR);
CREATE DBA PROCEDURE clust_catalog(catalog CHAR);
GRANT EXECUTE ON clust_catalog(CHAR) to marty;
注意

dbspc_cleanup() 无需 DBA 过程来调用 DBA 过程。

在 SPL 例程中查找错误​

当您以 DB-Access 使用 CREATE PROCEDURE 或 CREATE FUNCTION 来编写 SPL 例程时，如果在例程体中发生语法错误，则当您从菜单选择 Run时，该语句失败。

如果您正在 DB-Access 中创建例程，当您从菜单选择 Modify 选项时，光标移动至包含语法错误的那一行。您可再次选择 Run 和 Modify 来检查后续的行。

编译时刻警告​

如果数据库服务器检测到潜在的问题，但该 SPL 例程的语法是正确的，则数据库服务器生成警告，并将它放置在列表文件中。您可在执行该例程前，检测此文件来检查潜在的问题。

在 CREATE PROCEDURE 或 CREATE FUNCTION 语句的 WITH LISTING IN 子句中指定文件名称和列表文件的路径名称。要获取关于如何指定列表文件的路径名称的信息，请参阅 指定 DOCUMENT 子句。

如果您正在网络上工作，则在该数据库驻留的系统上创建列表文件。如果您为该文件提供绝对的路径名称和文件名称，则在您指定的位置创建该文件。

对于 UNIX™，如果您为列表文件提供相对的路径名称，则在数据库驻留的计算机上您的 home 目录中创建该文件。（如果您没有 home 目录，则在 root 目录中创建文件。）

对于 Windows™，如果您为列表文件提供相对的路径名称，则如果该数据库在本地计算机上，则缺省的目录为您的当前工作目录。否则，缺省的目录为 %GBASEDBTDIR%\bin。

在您创建例程之后，您可查看在 WITH LISTING IN 子句中指定的文件，来查看它包含的警告。

生成例程的文本​

在您创建 SPL 例程之后，将它存储在 sysprocbody 系统目录表中。sysprocbody 系统目录表包含可执行的例程，以及它的文本。

要检索例程的文本，请从 sysprocbody 系统目录表选择 data 列。文本条目的 datakey 列有代码 T。

下图中的 SELECT 语句读取 SPL 例程 read_address 的文本。

图: 读取 SPL 例程的文本的 SELECT 语句。

SELECT data FROM gbasedbt.sysprocbody
WHERE datakey = 'T'                   -- 查找文本行
AND procid =
( SELECT procid
FROM gbasedbt.sysprocedures
WHERE gbasedbt.sysprocedures.procname =
'read_address' )

调试 SPL 例程​

在您成功地创建并运行 SPL 例程之后，您可遇到逻辑错误。如果例程有逻辑错误，则请使用 TRACE 语句来帮助查找它们。您可跟踪下列项的值：

  • 变量
  • 参数
  • 返回值
  • SQL 错误代码
  • ISAM 错误代码

要生成被跟踪的值的列表，首先请使用 SQL 语句 SET DEBUG FILE 来命名包含被跟踪的输出的文件。当您创建 SPL 例程时，请包括 TRACE 语句。

下列方法指定 TRACE 输出的形式。

语句操作
TRACE ON跟踪 SQL 语句之外的所有语句。在使用变量之前，打印它们的内容。跟踪例程调用和返回的值。
TRACE PROCEDURE仅跟踪例程调用和返回的值。
TRACE expression打印文字或表达式。如果必要，在将表达式的值发送至文件之前，计算它。

下图演示您可如何使用 TRACE 语句来监视 SPL 函数执行的方式。

图: TRACE 语句。

CREATE FUNCTION read_many  (lastname CHAR(15))
RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15),
CHAR(2), CHAR(5);

DEFINE p_lname,p_fname, p_city CHAR(15);
DEFINE p_add CHAR(20);
DEFINE p_state CHAR(2);
DEFINE p_zip CHAR(5);
DEFINE lcount, i INT;

LET lcount = 1;

TRACE ON;      -- 从此开始跟踪每个表达式
TRACE 'Foreach starts';  -- 跟踪带有文字的语句

FOREACH
SELECT fname, lname, address1, city, state, zipcode
INTO p_fname, p_lname, p_add, p_city, p_state, p_zip

FROM customer
WHERE lname = lastname
RETURN p_fname, p_lname, p_add, p_city, p_state, p_zip
WITH RESUME;
LET lcount = lcount + 1;   -- 对返回的地址计数
END FOREACH

TRACE 'Loop starts';       -- 另一文字
FOR i IN (1 TO 5)
BEGIN
RETURN i , i+1, i*i, i/i, i-1,i WITH RESUME;
END
END FOR;

END FUNCTION;

使用 TRACE ON 语句，在您每次执行被跟踪的例程时，都将条目添加到您在 SET DEBUG FILE 语句中指定了的文件。要查看调试条目，请使用任何文本编辑器来查看该输出文件。

下列列表包含前面的示例中函数生成的一些输出。每一被跟踪的语句之后是对它的内容的解释。

语句操作
TRACE ON回送 TRACE ON 语句。
TRACE Foreach starts在此情况下，跟踪表达式，文字字符串 Foreach 开始。
start select cursor提供打开游标来处理 FOREACH 循环的通知。
select cursor iteration提供选择游标的每一迭代的开始的通知。
expression: (+lcount, 1)对遇到的表达式 (lcount+1) 求值为 2。
let lcount = 2以该值回送每一 LET 语句。

异常处理​

您可使用 ON EXCEPTION 语句来捕获数据库服务器返回给您的 SPL 例程的任何异常（或错误），或该例程产生的任何异常。RAISE EXCEPTION 语句允许您生成 SPL 例程内的异常。

在 SPL 例程中，您不可使用异常处理来处理下列情况：

  • 成功（返回了行）
  • 成功（未返回行）

错误捕获与恢复​

ON EXCEPTION 语句提供捕获任何错误的机制。

要捕获错误，请将一组语句包含在以 BEGIN 与 END 标记的语句块中，并在该语句块的开头添加 ON EXCEPTION IN 语句。如果在跟在 ON EXCEPTION 语句之后的块中发生错误，则您可采取恢复措施。

下图展示语句块内的 ON EXCEPTION 语句。

图: 捕获错误。

BEGIN
DEFINE c INT;
ON EXCEPTION IN
(
-206, -- 表不存在
-217  -- 列不存在
) SET err_num

IF err_num = -206 THEN
CREATE TABLE t (c INT);
INSERT INTO t VALUES (10);
-- 在插入语句之后继续
ELSE
ALTER TABLE t ADD(d INT);
LET c = (SELECT d FROM t);
-- 在选择语句之后继续
END IF
END EXCEPTION WITH RESUME

INSERT INTO t VALUES (10);  -- 如果 t 不存在，则失败

LET c = (SELECT d FROM t);  -- 如果 d 不存在，则失败
END

当发生错误时，SPL 解释器搜索捕获该错误的最内层 ON EXCEPTION 声明。捕获错误之后的第一个操作是重置该错误。当完成错误操作代码的执行时，且如果引起错误的 ON EXCEPTION 声明包括了 WITH RESUME 关键字，则以跟在产生了该错误的语句之后的语句自动地恢复执行。如果 ON EXCEPTION 声明未包括 WITH RESUME 关键字，则执行完全地退出当前的块。

ON EXCEPTION 语句的控制作用域​

ON EXCEPTION 语句的作用域从紧跟在 ON EXCEPTION 语句之后的语句扩展，并结束于在其中发出 ON EXCEPTION 语句的语句块的末尾。如果 SPL 例程未包括显式的语句块，则作用域为该例程中所有后续的语句。

对于在 IN 子句中指定的异常（或对于所有异常，如果未指定 IN 子句的话），ON EXCEPTION 语句的作用域包括同一语句块内跟在 ON EXCEPTION 语句之后的所有语句。如果在那个块内嵌套其他语句块，则该作用域还包括跟在 ON EXCEPTION 语句之后的嵌套的语句块中的所有语句，以及在那些嵌套的块内嵌套的语句块中的任何语句。

下列伪代码展示在例程内该例程为有效的位置。 即，如果错误 201 发生在任何指示了的块中，则发生标号为 a201 的操作。

图: ON EXCEPTION 语句的控制作用域。

CREATE PROCEDURE scope()
DEFINE i INT;
. . .
BEGIN          -- 开始语句块 A
. . .
ON EXCEPTION IN (201)
-- 执行操作 a201
END EXCEPTION
BEGIN         -- 嵌套的语句块 aa
-- 执行操作，a201 在此有效
END
BEGIN         -- 嵌套的语句块 bb
-- 执行操作，a201 在此有效
END
WHILE i < 10
-- 执行某操作，a201 在此有效
END WHILE

END    -- 语句块 A 的末尾
BEGIN           -- 开始语句块 B
-- 执行某操作
-- a201 在此 NOT 有效
END
END PROCEDURE;

用户生成的异常​

您可使用 RAISE EXCEPTION 语句生成您自己的错误，如下图所示。

图: RAISE EXCEPTION 语句。

BEGIN
ON EXCEPTION SET esql, eisam   -- 捕获所有错误
IF esql = -206 THEN          -- 未找到表
-- 某种恢复
ELSE
RAISE exception esql, eisam;  -- 放过该错误
END IF
END EXCEPTION
-- 执行某操作
END

在该示例中，ON EXCEPTION 语句使用两个变量 esql 和 eisam，来保存数据库服务器返回的错误编号。如果发生错误且如果 SQL 错误编号为 -206，则执行 IF 子句。如果捕获任何其他 SQL 错误，则将它从此 BEGINEND 块传至前面的示例的最后 BEGINEND 块。

模拟 SQL 错误​

您可产生错误来模拟 SQL 错误，如下图所示。如果用户为 pault， 则 SPL 运行如同那个用户没有 update 权限一样，即使该用户实际确实拥有那个权限。

图: 模拟 SQL 错误。

BEGIN
IF user = 'pault' THEN
RAISE EXCEPTION -273;  -- 拒绝 Paul 的 update 权限
END IF
END

使用 RAISE EXCEPTION 来退出嵌套的代码​

下图展示您可如何使用 RAISE EXCEPTION 语句来退出深度嵌套的块。

图: RAISE EXCEPTION 语句。

BEGIN
ON EXCEPTION IN (1)
END EXCEPTION WITH RESUME -- do nothing significant (cont)

BEGIN
FOR i IN (1 TO 1000)
FOREACH select ..INTO aa FROM t
IF aa < 0 THEN
RAISE EXCEPTION 1;     -- emergency exit
END IF
END FOREACH
END FOR
RETURN 1;
END

--do something;               -- emergency exit to
-- this statement.
TRACE 'Negative value returned';
RETURN -10;
END

如果最内层的条件为真（如果 aa 为负），则发生异常，且执行跳至跟在该块的 END 之后的代码。在此情况下，执行跳至 TRACE 语句。

请记住，BEGINEND 块为单个语句。如果在块中的某处发生错误，且在该块之外捕获，则当执行恢复时，跳过该块剩余的部分，并从下一语句开始执行。

除非您在该块的某处为此错误设置捕获，否则，将错误条件传回至包含该调用的块，并传回至包含该块的任何块。如果不存在设置处理该错误的 ON EXCEPTION 语句，则停止该 SPL 例程的执行，为正在执行该 SPL 例程的例程创建一个错误。

检查 SPL 例程中处理的行数​

在 SPL 例程内，您可使用 DBINFO 行数来找出在 SELECT、INSERT、UPDATE、DELETE、EXECUTE PROCEDURE 和 EXECUTE FUNCTION 语句中已处理了的行数。

下图展示一个 SPL 函数，使用带有 'sqlca.sqlerrd2' 选项的 DBINFO 行数，来确定从表删除的行数。

图: 确定从表删除的行数。

CREATE FUNCTION del_rows ( pnumb INT )
RETURNING INT;

DEFINE nrows INT;

DELETE FROM sec_tab WHERE part_num = pnumb;
LET nrows = DBINFO('sqlca.sqlerrd2');

RETURN nrows;

END FUNCTION;

要确保有效的结果，请在已执行完毕的 SELECT 和 EXECUTE PROCEDURE 或 EXECUTE FUNCTION 语句之后使用此选项。此外，如果您在游标内使用 'sqlca.sqlerrd2' 选择，请确保在关闭游标之前访存所有行，来保证有效的结果。

总结​

SPL 例程为提高您的数据库处理效率提供许多机会，包括提升数据库性能，简化应用程序，以及限制或监视对数据的访问。您还可使用 SPL 例程来处理扩展的数据类型，诸如集合类型、row 类型、opaque 类型和 distinct 类型。要了解 SPL 语句的语法图，请参阅《GBase 8s SQL 指南：语法》。